DATEDIF: The Proverbial Black Sheep of the Excel Family..?

In this article we look at a somewhat obscure Excel function. Why is DATEDIF seemingly shunned by Microsoft? By Liam Bastick, Managing Director (and Excel MVP) with SumProduct Pty Ltd.

Query

I have received a financial model which uses the Excel function DATEDIF on several occasions. I cannot seem to find anything about this function in Excel Help. Is it a “real” function? If so, how does it work?

Advice

DATEDIF can be a useful function when working with dates:

DATEDIF is one of several Excel date functions that can be used to calculate the number of days between two dates, which in this case, can also be performed by simply subtracting one date from another. However, it can also be used to calculate the number of complete months or years.

To use DATEDIF you must type the function in manually; it will be recognised by Excel. Indeed, although this function is available in all versions of Excel since Excel ’95, it has been documented in Microsoft’s Help file once only, in Excel 2000. Fellow MVP Chip Pearson refers to DATEDIF as “…the drunk cousin of the Formula family…”. It seems as though we mustn’t talk about it in polite company.

The syntax for the DATEDIF function is:

The function has three arguments that need to be entered as part of the function:

• start_date – Required, this is the start date of the chosen time period;
• end_date – Also required, this is the end date of the chosen time period; and
• “interval” – This must also be entered and mandates whether the function should return the number of days (“d”), complete months (“m”) or complete years (“y”) between the two dates specified. The syntax for the interval is strict: the letters must be entered between inverted commas.

In fact, the interval argument can also contain a combination of days, months and years in order to increase the variety of answers returned by the function. For example:

• “ym” – calculates the number of complete months between two dates as if the dates were in the same year;
• “yd” – calculates the number of days between two dates as if the dates were in the same year; and
• “md” – calculates the number of days between two dates as if the dates were in the same month and year.

Watch out for two common error messages with this function:

• #VALUE! appears in the answer cell If one of DATEDIF‘s arguments is not a valid date (e.g. the date was entered as text); and
• #NUM! occurs in the result cell if the start_date is larger (i.e. later in the year) than the end_date argument.

You can find more on dates here: Asking for a Date.

Word to the Wise

There may be a reason why this function might have been swept under the carpet by Microsoft…

If you are using Excel 2007 and have incorporated Service Pack 2 (SP2) onwards, the following formula:

gives the wrong answer. It works in all other versions of Excel, even Excel 2007 with SP1. Absolutely bizarre, but true nonetheless. As a tip, for those of you with Power Pivot, the YEARFRAC function in Power Pivot works similarly but appears to handle this bug.

It might be worth using caution with this enigmatic function.