DATEDIF Function

The DATEDIF function returns the number of units between two dates.
This function was originally provided to allow backwards compatibility with other spreadsheet programs.


Invisible Function

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


Units

There are 6 different units that you can pass in
These can be placed into cells with a cell reference or passed directly into the function using speech marks
SS


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





Important

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