DATEDIF Function

The DATEDIF function returns the number of units between two dates.
In Excel 2007 there were a number of defects.
In Excel 2010 some of these were fixed but not all of them.
In Excel 2010 this function was officially classified as "unsupported".
In Excel 365 this function can still be used although it is only included for backwards compatibility reasons.
The official support page has not been updated recently and contains misleading information.

Invisible Function

This function does not appear in the 'Insert Function' dialog box or on the Microsoft Online website
It is not recognised in the Function Arguments dialog box.

Common Uses

Calculating the number of days between two dates
Calculating the number of "whole" months between two dates
Calculating the number of "whole" years between two dates

The Alternative Formulas

Despite this function being unsupported there is no equivalent function to take its place.
The DATEDIF function can return 6 different pieces of information about two dates.

These can be placed into cells with a cell reference or passed directly into the function using speech marks

Displaying the Difference

You can alter the formula slightly to remove any zero components.

Leap Years

The following are leap years:
2008, 2012, 2016, 2020, 2024, 2028
1900 was not a leap year although Excel thinks it was.
Excel thinks 29 Feb 1900 is a valid date.

=DATEDIF(B49,C49,"y")&" years "&DATEDIF(B49,C49,"ym")&" months "&DATEDIF(B49,C49,"md")
=IF(DATEDIF(B52,C52,"y")=0,"",DATEDIF(B52,C52,"y")&" years ")&IF(DATEDIF(B52,C52,"ym")=0,"",
=DATEDIF(B61,TODAY(),"y")&" years "&DATEDIF(B61,TODAY(),"ym")&" months "&DATEDIF(B61,TODAY(),"md")&" days"

Known Bugs

DateDif(31/12/2016, 28/02/2017, "M") = 1 (this should be 2)
DateDif(31/12/2016, 1/3/2017, "MD") = -2 (this should be 1)
DateDif(28/01/2017, 1/3/2017, "MD") = 1 (this should be 32) - this should be the days ignoring months and years


This function is very different to the VBA function - DATEDIFF function.
More examples can be found on Chip Pearsons website.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext