How to calculate years and months between dates

TheDATEDIF functionis a “compatibility” function that comes from Lotus 1-2-3.

DATEDIF takes three arguments: start date, end date, and unit.

To explain how DATEDIF works, the table below is set up to show all options available for Unit.

First, however, let’s calculate both years and months in a different way.

This will give you some data to compare with the results of DATEDIF.

To calculate years between dates, we can use a function called YEARFRAC.

YEARFRAC returns the number of years between dates, including fractional years.

This is a handy function for figuring out something like age when you have a birthdate.

Unfortunately, there is not a similar function to calculate months in Excel.

For that, we need to create our own formula.

First, we subtract the start year from the end year and multiply the result times 12.

This gives us total months in the full years between the two dates.

I’ll also calculate days, just to have that number available, too.

Now let’s look at how DATEDIF works.

When you enter DATEDIF, you won’t get any function screen tips since it’s not documented.

In cell E8, you might see that we get “3” for complete years.

YEARFRAC gives us the same number but includes the fractional value as well.

E9 is the difference in complete months.

We get “38” with both DATEDIFandour own formula.

With unit as “D,” we get “1155” days.

The same as our manual calculation.

That’s because the start date and end date are both the first of the month.

If I change the end date to March 15th, we’ll get “14” days.

With unit as YM, we get “2” for months.

This is the difference between January and March when day and year are ignored.

Finally, with unit as YD, we get “59” days when years are ignored.

This option calculates days as if both dates are always in the same year.