The DATEDIF function is a hidden/undocumented function in Excel that allows you to calculate the number of years, months or days between two dates. The function would for example allow you to calculate the age of an individual.
When you type =DATEDIF into a cell notice that the function doesn’t come up in the list of available functions – so there is no screen tip to help you construct your formula. Luckily DATEDIF is relatively easy to use.
DATEDIF has three arguments. =DATEDIF(Date1, Date2, Interval)
The Interval argument allows the following values:
| Y | Calculate the number of complete years |
| M | Calculate the number of complete months |
| D | Calculate the number of days |
| YM | Calculate the number of months excluding years (see the video for a full explanation) |
| MD | Calculate the number of days excluding years and months (see the video for a full explanation) |
| YD | Calculate the number of days excluding years(see the video for a full explanation) |
Now although the function will only enable you to calculate on one of these intervals at a time, you can quite easily concatenate multiple DATEDIF functions to give you a years, months and days calculation.
In the video above the following formula is constructed to calculate the difference between the dates in cells A1 and B1.
=DATEDIF(A1,B1,”Y”)&” years “&DATEDIF(A1,B1,”YM”)&” months “&DATEDIF(A1,B1,”MD”)&” days”
NOTE the date1 must earlier than date2 or the DATEDIF function will return the #NUM! error.