DATEDIF Function in Microsoft Excel

The undocumented DATEDIF function can be incredibly useful when calculating the difference between dates in Excel. It is a bit of a hidden gem, as it doesn't appear in the list of formulas or if you search for it using the Insert Function dialog box. In fact, even when you are typing in the formula, the small screen tips do not appear, as they do with a number of other functions.

It can be used in many different ways, but perhaps one of its most common uses is to calculate a person's age.

Using the DATEDIF Function

Take the scenario below as an example:



Step 1. We know the date of birth of the candidates and the current date is in H1 in the form of =TODAY()

Step 2. So we can insert a little formula in C2 to work out their age. Type in =DATEDIF(B2, $H$1, "Y")



The first part of the formula refers to the first date value (i.e. the DOB). The second part refers to the end date (i.e. today's date) and the final part ("Y") represents how you want to display the interval between the two dates.

In this example, "Y" is used, so the answer will display the number of complete years between the two dates. It's great to calculate ages because it will not round the figure up.

You can use this formula to calculate the difference between dates in years, days and months. You can also work out the difference between months, ignoring the year and day, and the difference between days, ignoring the month and the year or just ignoring the year.

So the difference in days between 1st Jan 2014 and the 5th Feb 2015 using the "YD" interval, is 35.

Date Intervals and Explanations

Y: The number of complete years.
M: The number of complete months.
D: The number of days.
MD: The difference between the days (months and years are ignored).
YM: The difference between the months (days and years are ignored).
YD: The difference between the days (years are ignored).