Want to calculate the number of months between two dates? One little known way is to use the secret Excel DATEDIF function.
Try typing =DATEDIF into Excel and it will pretend it doesn’t recognise it – see how it’s not in the list of functions to the left.
But if you know the syntax, which I’ll share with you in a moment, you can use it for a range of applications.
I suspect it's secret because it can be a bit problematic and occasionally returns errors! You should use it with caution. I'll talk more about the known issues with the Excel DATEDIF function in a moment.
Microsoft say they only include DATEDIF in recent versions for backward compatibility, which is lucky for us because it’s a really handy function.
Now, as you’d expect from its name the DATEDIF function calculates the difference between two dates.
Excel DATEDIF Function syntax
Date1 is the start date
Date2 is the end date
Unit is the type of unit you want to calculate e.g. days, months, years.
D = days. Complete days between two dates.
M = months. Complete calendar months between two dates.
Y = years. Complete calendar years between two dates.
YD = Complete calendar days between two dates as though the end date is in the same year as the start date.
YM = Complete calendar months between two dates as though the end date is in the same year as the start date.
MD = complete calendar days between two dates as though the month and year of the end date is the same as the start date.
Caution! The "M" and "MD" arguments have known limitations. I'll show you some examples soon.
Note: DATEDIF calculations do not include the start date in the resultant count, and they only return whole numbers.
Let’s take a look at some DATEDIF examples:
Note: as I'm in Australia all the dates in this example are dd/mm/yyyy. Please modify your dates to match your region settings when practicing with these examples.
BTW: you could achieve the same result for example 1 above with this formula:
Ok, so the above are some pretty basic calculations.
Let’s have some fun with DATEDIF now, and in keeping with the Queen’s Silver Jubilee we’ll use Her Majesty as an example.
Fun # 1:
Years since Queen Elizabeth II ascended the throne:
Note: in the above formula TODAY() = 6th June 2012. The TODAY() function will return today’s date as per your computer clock. Also handy for report headers and the like.
Fun # 2:Queen Elizabeth’s age today (6th June 2012) in days, months and years:
=DATEDIF("21/04/1926",TODAY(),"y")&" years,"&DATEDIF("21/04/1926",TODAY(),"ym") &" month(s), "&DATEDIF("21/04/1926",TODAY(),"md")&" Days"
= 86 years, 1 month(s), 16 Days
The above formula uses the concatenation technique to join text and numbers together.
Fun # 3:
Calculate how long until my next holiday.
Suppressing 0 values where there are no years or months returned:
="Only "&IF(DATEDIF(TODAY(),"17/03/2013","y")=0,"",DATEDIF(TODAY(),"17/03/2013","y")&" years ")&IF(DATEDIF(TODAY(),"17/03/2013","ym")=0,"",DATEDIF(TODAY(),"17/03/2013","ym")&" months ")&DATEDIF(TODAY(),"17/03/2013","md")&" days"
= Only 9 months 11 days
This technique uses an IF function to first evaluate whether there are any years to count, if not return nothing (as stipulated by the empty double quotes ""), and so on for months and days.
Ideas for Using DATEDIF:
- Your age in days, months and years. When you get older this gets harder to keep track of 🙂
- Length of service of an employee.
- Equipment age.
- Countdown to a date.
Excel DATEDIF Function Errors
- If Date 1 is later than Date 2 Excel will return a #NUM error.
- If Date 1 or Date 2 is not a valid date you will get a #VALUE error.
- If the unit is not one of the above options Excel will return a #NUM error.
The table below illustrates some examples of the DATEDIF function. The problematic units are M and MD. Cell E36 result should be 2! Something is not right with Feb 28, 2017. And cell E43 contains an example of MD returning erroneous results. It should be 1, not -2. Cells E44 & E45 also return inconsistent results.
Now you see how unreliable DATEDIF can be, it's probably better to err on the side of caution and use a different function to achieve the same results. *Note the M substitutes either round the months up or down. There is no direct equivalent to DATEDIF with Unit "M". Likewise, the alternate to MD doesn't replicate perfectly in every scenario.
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.