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 "compatability" 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 3 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 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. As you can see, YEARFRAC returns the number of years between two dates, including fractional years.
This is a handy function for figuring out something like age when you have a birthday.
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 the months in the full years between the 2 dates.
Then we add the difference between the end month and the 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 tips since it's not documented. The first date is the start date, the second date is the end date, and unit is entered as text. It 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 the units exposed on the worksheet, so I can just point back to column D and copy the formula down.
In E8, you can see we get 3 complete years. YEARFRAC gives us the same number, but including 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 zero for days, when both month and year are ignored. THat's becasue both the start date and the end date are the first of a month. If I change end date to March 15, we'll get 14 days.
With Unit as YM, we get 2 for months, because days and year are ignored. This is the difference between January and March.
Finally, with Unit as YD, we get 59 days when years are ignored. This calculates days as if both dates where always in the same year.
The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel...
The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the...
The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the...