It came upon the midnight clear
That glorious song of old
I want to calculate the difference between 2 dates in Excel
Or be left out in the cold.

Yes, the Poorhouse was confronted with the above situation; namely that given a date1 and a date2 in Microsoft Excel we needed to know the difference. Not the difference in terms of random Excel microseconds since 2nd August 1957 type numbers, but an actual, readable "3 years 2 months" type difference.

Perhaps this doesn't seem like quite the craziest of plans; surely people are forever wanting to calculate someone's age, length of service and so on? But as far as Mr Excel is concerned it is a task as arcane and bewildering as the search for the Holy Grail.

An extensive tour of the Microsoft Developers Network allowed us to formulate this less-than-concise solution. If your dates to compare are in cells A1 and A2, this bad-boy gives you the years and months between in a nice readable format.

Looking at that formula did not improve the health of the Poorhouse, so further research was done. This revealed a hidden treasure; the top secret built-in Excel DATEDIF function. This is not the same as the datediff() function in VBA and Access which is not available as a standard Excel function, and yes, it has only one F. Furthermore for some strange reason it appears to be entirely undocumented in Excel help even though it has featured since time began…

DATEDIF works like this:

The two dates are those that you want to compare (if you get them the wrong way round you'll get a #NUM! error).

Interval can be any of:

• "y" - how many complete years are between the dates
• "m" - how many complete months are between the dates
• "d" - how many complete days are between the dates
• "ym" - how many months are between the dates were they to be in the same year
• "yd" - how many days are between the dates were they to be in the same year
• "md" - how many days are between the dates if they were both in the same year, and both in the same month

The "ym", "yd" and "md" are the particular treats. If it not by yet obvious why, then the equivalent to the Microsoft jibberish above is:

For more information, why not baffle yourself by letting Microsoft explain how to use dates and times in Excel. Potentially more useful is the wonderful Pearson Software Consulting Excel pages upon which the Poorhouse's metaphorical eyes were first opened to the DATEDIF function; and, if you'll excuse the technical terminology, it contains an immense pile of other useful stuff.