Datedif in Depth

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

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

Mike Barrett (MCT) Senior Training Advisor