### 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 microsoft.com 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.

SS

#### 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

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