Secret DateDif Function in Excel Calculates Years, Months, Days

August 14, 2014 - by Bill Jelen

Sometimes, you need to show each employee’s age in Years and Months. The cool|secret|sometimes buggy =DATEDIF function does the trick.

This function has not been documented since Excel 2000, so figuring out the third argument is the trick. Enter "Y" to get full years. But, if you enter "M", you are going to get the total number of months. Someone who is 49 years old will be reported as 588 months old, which is usually not what you are looking for. Instead, use "YM", which I remember as "Months in excess of the full years reported by Y".

For Days, you have "D" for total days (18075), or "YD" for days in excess of full years, or "MD" for days in excess of full months.

Why is this undocumented and sometimes buggy? If you have someone born on January 31 and you have an ending date of March 1, the YM reports 1 month and the MD reports Negative One days. But really, who actually employs one-month old babies these days? There are other strange pairs of dates which failed to work in Excel 2007 SP2, although those appear fixed in Excel 2010.

PS – Wow – it looks like I am 3 days away from my 49.5 half birthday! I better alert my family...