The hidden trap in Excel’s DateDif()

There’s plenty of concern about Microsoft Excel’s DateDif function but there’s also a hidden trap that rarely gets a mention and is too easy to overlook.

DateDif() is a long-standing Excel function that returns the duration between two dates.  It’s not a ‘full’ Excel function because it was added for compatibility with Lotus 123 and has a known bug.

Despite that, DateDif() is really, really useful so it’s widely used see Calculate age and other interesting facts using Excel and DateDif() for just some uses.

In this article we’ll highlight a trap in DateDif() and any other calculation of number of days between two dates. It’s so simple, it’s easy to overlook, we’ve done it ourselves.

Four ways to calculate duration

There are four ways to calculate the duration between two dates with three different results possible.

  • EXclude both start and end date –  only count the whole days between the two dates.
    • Between 1 January and 4 January – result 2  (2nd and 3rd Jan only)
  • INclude both start and end date – count the whole days with the start and end dates included.
    • Between 1 January and 4 January – result 4  (1st, 2nd 3rd and 4th  Jan)
    • Use this option for situations like calculating based on a daily rate.

That might not seem important, but it makes a difference in many like calculating wages.

For DateDif() it’s important when you’re looking at the number of whole months and one of the dates is at the end of a month.

  • INclude the start date, EXclude the end date
    • Between 1 January and 4 January – result 3  (1st,2nd and 3rd Jan)
    • How DateDif() calculates by default.
  • EXclude the start date, INclude the end date
    • Between 1 January and 4 January – result 3  ( 2nd 3rd and 4th  Jan)

DateDif() only counts start date

DateDif() includes the start date in its count but ignores the end date.  See this simple example:

Between 26 February and 29 February, DateDif() counts three days.  Not 4 days or 2 days, if other methods were used.

The problem for DateDif() is clearer when you see this example, the difference between 31 Jan and 28 Feb 2021 (the last day of the month in a non-leap year) showing the number of whole months.

DateDif() says there are 28 days between the two dates (row 17) – correct.

In row 18, DateDif() counting whole months (“m”) says there are zero. 

Some commentators suggest that’s a bug, but we don’t think so because DateDif() does NOT count the end date.  Since the function is only counting ‘up to and including’ 27 Feb (i.e the day before the end date), the whole month of February hasn’t completed, by DateDif() logic.

Fixing DateDif()

Adjust DateDif() by hacking the start and end dates to make sure they are included or excluded as necessary. 

Not only do these changes affect the number of days counted, it can also change the result for a count of whole months (“m” parameter in DateDif()) or whole years (“y”) if the original dates are at either end of a month or year.

All you have to do is add one day, as required.

INclude start and end date

Add one to the end date (second) parameter.

EXclude start date

Add one to the start date (first) parameter.

EXclude start date, INclude end date

Add one to BOTH start and end date parameters.

Might not be necessary because this returns the same number of days as the DateDif() default, but can affect results for whole months “m” or whole years “y”.

INclude start date, EXclude end date

No change necessary, it’s the DateDif() default.

Beware the date calculation trap in Excel
Calculate age and other interesting facts using Excel and DateDif()
Better Excel formula converts days into Years, Months, Days

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address