Your required to calculate the number of months between two dates? You’ll be needing the **Hiden** 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:**

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

=C4-B4

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.

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