How to calculate years and months between dates

In this video we'll look at how to calculate the number of years or months between dates using a function called DATEDIF and a function called YEARFRAC.

The DATEDIF function is a "compatibility" function that comes from Lotus 1-2-3. For reasons that are unknown, it's only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.

DATEDIF takes three arguments: start date, end date, and unit. Unit specifies whether you want the difference between dates in days, months, or years, with several variations.

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. The YEARFRAC function takes three arguments: start date, end date, and an optional parameter called "basis" that we'll ignore for now.

I'll plug in the dates we already have in columns B and C so you can see how it works. 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.

Next, we add the difference between the end month and start month to get the remaining months between the 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. The first date is "start" date, and the second date is "end" date, and "unit" is entered as text. "Unit" must be one of the values you see in column D.

To enter "unit" directly in the formula, enclose the value in double quotes.

In this case, though, we have units exposed on the worksheet, so I can just point back to column D and copy the formula down.

In cell E8, you can 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 DATEDIF and our own formula.

With unit as "D," we get "1155" days. The same as our manual calculation.

With MD as unit, we get "0" for days, when both month and year are ignored. 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.