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 DateEnd DateDate DiffFormula
1/07/20206/07/20205=DATEDIF(B8,C8,”d”)
1/07/20201/08/202031=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 DateEnd DateDate DiffFormula
1/07/20206/07/20205=H8-G8
1/07/20201/08/202031=H9-G9

Complete Date Difference, ignoring year

To ignore year, put “yd” in the time_unit argument as such:

Start DateEnd DateDate DiffFormula
1/07/20201/08/202031=DATEDIF(B13,C13,”yd”)
1/07/20201/07/20210=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 DateEnd DateDate DiffFormula
1/07/20201/08/202031=DATE(YEAR(G13), MONTH(H13), DAY(H13))-G13
1/07/20201/07/20210=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 DateEnd DateWeek DiffFormula
01/07/202006/07/20200=ROUNDDOWN(DATEDIF($B11,$C11,”d”)/7,0)
01/07/20208/07/20201=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 DateEnd DateWeek DiffFormula
1/07/20206/07/20201=WEEKNUM(C12,1)-WEEKNUM(B12,1)
1/07/20209/07/20201=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 DateEnd DateDiffFormula
1/07/20206/07/20200=DATEDIF(B28,C28,”m”)
1/07/202031/08/20201=DATEDIF(B29,C29,”m”)
1/07/202031/08/202113=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 DateEnd DateDiffFormula
1/07/20206/07/20200=(YEAR(H28)-YEAR(G28))*12+(MONTH(H28)-MONTH(G28))
1/07/202031/08/20201=(YEAR(H29)-YEAR(G29))*12+(MONTH(H29)-MONTH(G29))
1/07/202031/08/202113=(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 DateEnd DateDiffFormula
1/07/20206/07/20200=DATEDIF(B34,C34,”ym”)
1/07/202031/08/20201=DATEDIF(B35,C35,”ym”)
1/07/202031/08/20211=DATEDIF(B36,C36,”ym”)

Calendar Month Difference, Ignoring year difference

Again, an alternative way to return similar result.

Start DateEnd DateDiffFormula
1/07/20206/07/20200=MONTH(H34)-MONTH(G34)
1/07/20201/08/20201=MONTH(H35)-MONTH(G35)
1/07/202031/08/20211=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.

calculate end date using duration as the reverse operation to calculate in Excel Number of Days

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:

Count by Hour

Reference:

https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

Pin It on Pinterest

Share This