### DATEDIF Function

The __DATEDIF__ function returns the number of units between two dates and is officially "unsupported".

In Excel 365 this function is still available for **backwards compatibility** but should not be used.

This function does not appear in the 'Insert Function' dialog box and is not recognised in the 'Function Arguments' dialog box.

The accuracy of this function was improved slightly in Excel 2010 but some of the bugs were never fixed.

#### Known Bugs (M, MD and YD units)

Cells shaded yellow are incorrect.

There are known bugs when you use the "M" unit.

There are known bugs when you use the "MD" unit.

There are known bugs when you use the "YD" unit.

#### Replacement Formula - M

Column "D" uses the DATEDIF function and often gives the incorrect answer.

Column "E" uses the formula shown.

This formula uses the following functions: __ROUNDDOWN__, __YEAR__, __MONTH__, __DAY__, __EOMONTH__

#### Replacement Formula - MD

Column "D" uses the DATEDIF function and often gives the incorrect answer.

Column "E" uses the formula shown.

This formula uses the following functions: __DATE__, __YEAR__, __MONTH__ and __DAY__

#### Replacement Formula - YD

Column "D" uses the DATEDIF function and gives the incorrect answer.

Column "E" uses the formula and gives the correct answer.

This formula uses the following functions: __EDATE__, __ROUNDDOWN__ and __YEARFRAC__

This formula always takes the year from the "end_date".

#### Replacement Formula - YM

Column "D" uses the DATEDIF function.

Column "E" uses the formula shown.

This formula uses the following functions: __MOD__, __YEAR__, __MONTH__ and __DAY__

#### Replacement Formula - Y

Column "D" uses the DATEDIF function.

Column "E" uses the formula shown.

This formula uses the following functions: __ROUNDDOWN__ and __YEARFRAC__

#### Replacement Formula - D

Column "D" uses the DATEDIF function.

Column "E" uses the formula shown.

This formula uses the function __INT__

#### User Defined Functions

These formulas have also been converted into 6 User Defined Functions.

You can also use the __DATEUNITS - User Defined Function__

Column "D" uses the DATEDIF function.

Column "E" uses the DATEUNITS user defined functions.

#### Years, Months, Days

Calculate the number of years, months, days between 2 dates.

=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"

#### Important

The Excel DATEDIF function is very different to the VBA function - __DATEDIFF__ function.

Excel thinks 1900 was a leap year.

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