Using the Undocumented DATEDIF() Function in Excel

I’ve written before (albeit briefly, as more of a footnote) about finding the difference between two dates in Excel. This approach, of subtracting one date from another is fine if you want to know the number of days between two dates – and of course you can go one step further and use NETWORKDAYS to identify how many of those days are business days.

But what if you need to know the number of months, or years between two dates. In T-SQL, you can usually use the DATEDIFF function, which allows you to provide a ‘Date Part’ to request number of days/week/months/years between two dates. But what about Excel?

As it happens, there’s almost identical functionality in Excel – except it’s not very well documented – in fact, you won’t know it exists until you try to use it.

The function is

=DATEDIF([Start Date],[End Date],[Date Part])

But, if we look through the list of ‘All’ available functions, there is no sign of this function

And, unlike most other functions, even if you start typing it out, Excel doesn’t prompt us on its usage.

Whereas a ‘Standard’ Function will…

So what’s the deal?

As it turns out, the function exists, as with many other Excel features, purely for compatibility with Lotus Notes 123 – a long since discontinued spreadsheet program.

Given there’s no direct alternative for DATEDIF, it’s not clear why this isn’t a fully implemented function, but it exists, and you can use it with most versions on Excel, including Excel Online and Google Sheets

To use it, simply provide a start and end date, then select from the list of available ‘Parts’ – you will get different behaviour depending on which ‘part’ you use.

(Dates are in dd/mm/yyyy format)

Eg,

=DATEDIF("01/01/2015","04/05/2016","M")

= 16

The first three are pretty self-explanatory, but the other three are quite interesting, and well worth having a play around with