Want to calculate the number of months between two dates? You’ll be needing the secret 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.
Who knows why it’s a secret.
For some reason Microsoft don’t think the DATEDIF function is worthy of any documentation since Excel 2000. In fact they say they only include it in recent versions for backward compatibility.
Which is lucky for us cause it’s a really handy function.
Now, as you’d expect from its name the DATEDIF function calculates the difference between two dates.
Date1 is the start date
Date2 is the end date
Interval is the type of interval 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.
Let’s take a look at some DATEDIF examples:
Since 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 untill my next holiday.
Supressing 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.
- 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 interval is not one of the above options Excel will return a #NUM error.
Thanks to one of our members, Greg Rosenberger, for reminding me to write about this function. I also had the need to use it again the other day myself.