logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Using the Undocumented DATEDIF() Function in Excel

October 4, 2016 0 comments Article Easy, 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)

datedif

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

Related

Tags: Datediff, Excel, Functions, Office

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

How did Microsoft’s Power BI come to lead the Gartner Magic Quadrant?

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2025 - Theme by ThemeinProgress