The function to calculate in Excel the number of days between dates is the DATEDIF() function. You can remember it more easily as the “Date Difference” function. The DATEIF() function calculates the exact number of date difference between two dates. Note that this is different from calendar unit difference as you shall see below.
NOTE: All the dates shown are in dd/MM/yyyy format, but it works the same if you have MM/dd/yyyy date format setting.
If you are using DateDif() function to calculate number of days in Excel, please make sure you read the complete post to avoid incorrect results!!
Calendar Difference section below shows a similar alternative. Compared to “Complete Difference”, the two will be different when DateDif() returns an error or if there is an actual calendar difference, such as calendar week, month or year difference.
All formulas works between leap/non-leap years.
Excel DateDif Function for Number of Days
You can use DATEDIF() function to get the number of dates in Excel has the following syntax:
=DATEDIF(start_date, end_date, time_unit)
For start_date and end_date, you can use DATE(year, month, date) or DATEVALUE(date_string).
For example, for Mar 23rd 2020 (input as 3/23/2020 in United States or 23/3/2020 in Britain and Australia) in cell A1, you can write it as
DATE( Year(A1), Month(A1), Day(A1) ) or
DATEVALUE(“23/3/2020”)
If you swap the position of start_date and end_date, you will end up with an error.
You may need to capture the error with IFERROR() OR combined IF() and ISERROR() function.
Here is a comparison of DATEDIF() using different time_unit arguments to find in Excel number of days difference:
DateDif Today For Excel Number of Days Between Today and Another Date
To find the date today, you use TODAY() function. This will update everytime you open or refresh the workbook.
To calculate the number of days in Excel between today and a future date, use this formula:
=DATEDIF( TODAY(), future_date ), where future_date is usually a DATE() or DATEVALUE() function.
To calculate the number of days between today and a past date, use this formula:
=DATEDIF( past_date, TODAY() )
where past_date is usually a DATE() or DATEVALUE() function.
The order is important. If you do not put the greater date second, you will get a #NUM! error. See Calendar Date Difference section for alternative formulas.
If you wish to fix the date today in Excel, use Alt+; (semicolon) shortcut to enter a fixed date.
DateDif Formula Showcase For Number of Days For Most Cases
The screenshot below sums up most of the sections that follow. Use the scroll bar to see also the calendar difference. Use this as your reference to find in Excel the number of days between dates.
Excel Number of Days Between Two Dates
To use DateDif() function to calculate in Excel the number of days between two dates, you need a “d” in the third argument.
Complete Date Difference
=DATEDIF(start_date, end_date, “d”)
| Start Date | End Date | Date Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 6/07/2020 | 5 | =DATEDIF(B8,C8,”d”) |
| 1/07/2020 | 1/08/2020 | 31 | =DATEDIF(B9,C9,”d”) |
Calendar Date Difference
This is an alternative to DateDif() function to get the number of days between dates in Excel, and returns the same result in most cases.
| Start Date | End Date | Date Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 6/07/2020 | 5 | =H8-G8 |
| 1/07/2020 | 1/08/2020 | 31 | =H9-G9 |
Complete Date Difference, ignoring year
To ignore year, put “yd” in the time_unit argument as such:
| Start Date | End Date | Date Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 1/08/2020 | 31 | =DATEDIF(B13,C13,”yd”) |
| 1/07/2020 | 1/07/2021 | 0 | =DATEDIF(B14,C14,”yd”) |
You can remember this as “Not y, just d” if it helps.
Calendar Date Difference, ignoring year
To achieve the same without DateDif() function, you will need the help of Date() or a combination of DateValue() function and text functions.
| Start Date | End Date | Date Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 1/08/2020 | 31 | =DATE(YEAR(G13), MONTH(H13), DAY(H13))-G13 |
| 1/07/2020 | 1/07/2021 | 0 | =DATE(YEAR(G14), MONTH(H14), DAY(H14))-G14 |
Number of Weeks Between Two Dates
There is no direct unit argument, “w”, to calculate in Excel the number of days in weeks between two dates but there is a workaround.
Complete Week Difference
To get DateDif weeks difference between two dates, use “d” notation in time_unit argument as such:
=RoundDown( DATEDIF(start_date, end_date, “d”) / 7, 0)
Here, DateDif() function calculates date difference, which is then divided by 7 to get the number of complete weeks. You then need to round it down to get the complete week difference.
| Start Date | End Date | Week Diff | Formula |
|---|---|---|---|
| 01/07/2020 | 06/07/2020 | 0 | =ROUNDDOWN(DATEDIF($B11,$C11,”d”)/7,0) |
| 01/07/2020 | 8/07/2020 | 1 | =ROUNDDOWN(DATEDIF($B12,$C12,”d”)/7,0) |
Calendar Week Difference
If you use the above method to calculate in Excel the number of days in terms of weeks between say 1st Jul 2020 (Wednesday) and
6th Jul 2020 (Monday), it will return 0.
To calculate the actual difference in calendar weeks, use this formula instead:
=WEEKNUM(end_date)-WEEKNUM(start_date)
This will calculate calendar week difference.
WeekNum() function has a second argument that specifies which day the week begins. In this case we assume that the week starts on Sunday, so it can be omitted. See Microsoft Support.
| Start Date | End Date | Week Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 6/07/2020 | 1 | =WEEKNUM(C12,1)-WEEKNUM(B12,1) |
| 1/07/2020 | 9/07/2020 | 1 | =WEEKNUM(C13,1)-WEEKNUM(B13,1) |
Number of Months Between Two Dates
Google Support documents that “Months and years are only counted if they are equal to or go past the “day.” For example, the function returns “4 months” between the dates 9/30/15 and 2/28/16 (even though the 28th is the last day of the month).”
DateDif Years And Months
To calculate the years and months difference between two dates, use “m” in time_unit argument.
=DATEDIF(start_date, end_date, “m”)
Complete Month Difference
To get DateDif months difference between two dates, use “m” notation in time_unit argument as such:
=DATEDIF(start_date, end_date, “m”)
This returns the absolute month difference between two dates.
| Start Date | End Date | Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 6/07/2020 | 0 | =DATEDIF(B28,C28,”m”) |
| 1/07/2020 | 31/08/2020 | 1 | =DATEDIF(B29,C29,”m”) |
| 1/07/2020 | 31/08/2021 | 13 | =DATEDIF(B30,C30,”m”) |
Calendar Month Difference
This is a way of not using DateDif() function to return the number of days in Excel.
| Start Date | End Date | Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 6/07/2020 | 0 | =(YEAR(H28)-YEAR(G28))*12+(MONTH(H28)-MONTH(G28)) |
| 1/07/2020 | 31/08/2020 | 1 | =(YEAR(H29)-YEAR(G29))*12+(MONTH(H29)-MONTH(G29)) |
| 1/07/2020 | 31/08/2021 | 13 | =(YEAR(H30)-YEAR(G30))*12+(MONTH(H30)-MONTH(G30)) |
Complete Month Difference, Ignoring year difference
You can remember the “ym” argument as “not year, but month”.
| Start Date | End Date | Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 6/07/2020 | 0 | =DATEDIF(B34,C34,”ym”) |
| 1/07/2020 | 31/08/2020 | 1 | =DATEDIF(B35,C35,”ym”) |
| 1/07/2020 | 31/08/2021 | 1 | =DATEDIF(B36,C36,”ym”) |
Calendar Month Difference, Ignoring year difference
Again, an alternative way to return similar result.
| Start Date | End Date | Diff | Formula |
|---|---|---|---|
| 1/07/2020 | 6/07/2020 | 0 | =MONTH(H34)-MONTH(G34) |
| 1/07/2020 | 1/08/2020 | 1 | =MONTH(H35)-MONTH(G35) |
| 1/07/2020 | 31/08/2021 | 1 | =MONTH(H36)-MONTH(G36) |
Calculate End Date Based on Duration and Start Date
Instead of calculating the number of days in Excel, this one seeks the end date, and requires a longer formula. The formula used here works for calendar unit difference. So for example, if you have 31st Mar 2020 as start date and put 1 month as duration, this will give you 31st Apr 2020 as end date.
The first date in the screenshot above is in cell A2. Formula for End Date would be:
=IFS(
NOT(ISERROR(SEARCH(“Day”,$B2,2))),$A2+VALUE(LEFT($B2,SEARCH(“Day”,$B2,2)-1)),
NOT(ISERROR(SEARCH(“Month”,$B2,2))),EDATE($A2,VALUE(LEFT($B2,SEARCH(“Month”,$B2,2)-1))),
NOT(ISERROR(SEARCH(“Year”,$B2,2))),DATE(YEAR(A2)+VALUE(LEFT($B2,SEARCH(“Year”,$B2,2)-1)),MONTH(A2),DAY(A2)))
This only works if you use a single unit in duration column. If you put a mix of say year and month, the formula will not work. You can however, convert 5 years to 5*36=180 months, and use that in the duration column to calculate end date based on the number of days you put.
This formula does not work on Mac Excel 2011 due to not having IFS() function. You can adapt it using multiple nested IF() function instead. See the post Google Sheets IF() Function with Examples.
DateDif Not Working
If the cell is returning an unexpected date, check that the cell does not have a custom date format applied. Select the cell then press Ctrl+1 to check number format.
If you put a later date first in place of start_date in the first argument to calculate the number of days in Excel between dates, you will get a #NUM! error. Simply swap the position of those two dates.
If the issue is with DATEDIF() not calculating correctly, Microsoft Support documented that “The “MD” argument may result in a negative number, a zero, or an inaccurate result.”
The solution proposed is to use standard date subtraction to calculate the difference between two dates.
DateDif Not In Excel
There will be no pop up cue when you type in “=DATEDIF()” in Excel. It is not promoted by Microsoft due to a few known bugs. However, if you follow the correct use of DATEDIF() described above, it will return a result.
How To Enable DateDif Function In Excel 2013, 2016, 2019,365
You do not need some special addon to enable DateDif() function in Excel. It is built into MS Excel and Mac Excel. However, you need to type out the entire formula since it is not included in the function library.
DateDif Google Sheets
DateDif() in Google Sheets work the same way as described above for MS Excel to calculate the number of days.
Summary on Excel Number of Days Calculation
When using DateDif() function it is vital that you be familiar with its limitations. The range of arguments used can be confusing itself.
This comprehensive guide goes through almost all situations you can encounter when calculating the number of days in Excel with or without DateDif() function.
This function was somewhat hidden and only documented since Excel 2016. You have learnt something only a handful of people know.
Share the post today to show everyone your progress!
Related Post:
Reference:
https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c
