Excel DATEDIF Function

Summary 

The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.

Note: Excel won't help you fill out the arguments for DATEDIF like other functions, but it will work when configured correctly.

Purpose 

Get days, months, or years between two dates

Return value 

A number representing time between two dates

Syntax 

=DATEDIF (start_date, end_date, unit)

Arguments 

• start_date - Start date in Excel date serial number format.
• end_date - End date in Excel date serial number format.
• unit - The time unit to use (years, months, or days).

Usage notes 

The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel 2000, but it works in all Excel versions since that time. As Chip Pearson says: DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation.

The DATEDIF function calculates the time between a start_date and an end_date in years, months, or days. The time unit to return is specified using the unit argument, which is supplied as text (upper or lower case).

Examples

In the example shown, column B contains the date January 1, 2016 and column C contains the date March 1, 2018. In column E:

E5=DATEDIF(B5,C5,"y") // returns 2
E6=DATEDIF(B6,C6,"m") // returns 26
E7=DATEDIF(B7,C7,"d")// returns 790

The table below summarizes available unit values and the result for each:

Unit	Result
"Y"	Difference in complete years
"M"	Difference in complete months
"D"	Difference in days
"MD"	Difference in days, ignoring months and years
"YM"	Difference in months, ignoring days and years
"YD"	Difference in days, ignoring years

Notes

1. Excel will not help you fill in the DATEDIF function like other functions.
2. DATEDIF with throw a #NUM error if start date is greater than the end date. If you are working with a more complex formula where start dates and end dates may be unknown, or out of bounds, you can trap the error with the IFERROR function, or use MIN and MAX to sort out dates.
3. Microsoft recommends not using the "MD" value for unit because it "may result in a negative number, a zero, or an inaccurate result". If you need to calculate days between dates ignoring year and month values, you can use the formula explained here.