DATEDIF Function

Microsoft training delivered by certified trainers, and a microsoft partner training company
excel training DATEDIF the Hiden Excel Function

Your required to calculate the number of months between two dates? You’ll be needing the Hiden DATEDIF function.

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 unless you type it all in.

Though a common feature used in Microsoft Access 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.

The DATEDIF function calculates the difference between two dates.

DATEDIF syntax =DATEDIF(date1,date2,interval)

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.

DATEDIF Interval Formats:

ym = Complete calendar months between two dates as though the end date is in the same year as the start date.

yd = Complete calendar days between two dates as though the end date is in the same year as the start date.

y = years. Complete calendar years between two dates.

md = complete calendar days between two dates as though the month and year of the end date is the same as the start date.

m = months. Complete calendar months between two dates.

d = days. Complete days between two dates.

Let’s examine some DATEDIF examples:

Excel DATEDIF Function

Note: you could achieve the same result for example 1 above with this formula:

Ok, so the above are some pretty basic calculations.

Example 1:
The number of Years since The Queen ascended the throne: =DATEDIF("6/2/1952",TODAY(),"Y")

Value Returned= 61

Note: in the above formula TODAY() = 6th may 2013. The TODAY() function will return today’s date as per your computer clock. Also handy for report headers and the like.

Example  2:
The Queens age today (6th may 2013) 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"

Value Returned=87 years,0 month(s), 15 Days

The above formula uses the concatenation method (see Text Functions).

Example 3:
Calculate how long untill my next holiday.

Supressing 0 values where there are no years or months returned:
="I Go On Holiday in "&IF(DATEDIF(TODAY(),"17/03/2014","y")=0,"",DATEDIF(TODAY(),"17/03/2013","y")&" years ")&IF(DATEDIF(TODAY(),"17/03/2014","ym")=0,"",DATEDIF(TODAY(),"17/03/2014","ym")&" months ")&DATEDIF(TODAY(),"17/03/2014","md")&" days"

Value Returned= I Go On Holiday in 10 months 11 days

the above formula will work as long as the date 17-03-14 is not exceeded

We use the 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 years, months and days. datedif1
  • Age is 51 Years, 7 Months and 5 Days

And the formula

="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Using the layout above and changing the labels try out the ideas below.

  • Length of service of an employee.
  • Equipment age.
  • Countdown to a date.

DATEDIF Errors Returned

  • 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.

Excel can be fun try out some of its features Detailed on this Site


Mike Barrett (MCT) Senior Training Advisor