Number of Months Between two Dates: "DateDif" and "Months" Return Wrong Values?

There are several options to calculate the time (in months) between a start date and an end date:


  • =MONTHS(DATE(2023,12,1),DATE(2024,2,29),1) returns 2, but it should be 3
  • =DATEDIF("2023-12-01","2024-02-29","m") returns 2 also, but it should be 3

How can I change this behavior, so all “included” and somehow affected months will be considered in the calculation?

Of course, I could simply add +1 everywhere, but that is not a nice solution…

MONTHS in mode 1 returns calendar dates.

2023-12-01 → 2023-12-31 = 0 Months
2023-12-01 → 2024-01-31 = 1 Months
2023-12-01 → 2024-02-29 = 2 Months

Even in mode 0 we get the same results because the 3rd month has not begun yet. It is still the last day of the second month, so we are still within the second month.
In mode 0 we calculate
2023-12-01 00:00:00 → 2024-02-29 00:00:00 = 2 Months including the first day but not including the last day which is also how DateDiff works.

Yes, math-wise this is correct for sure.

But when calculating employment contracts and one person is employed from 2023-12-01 until 2024-02-29, this is definitely three months. :grin:

How could I calculate this?

Just add one to the “end date” - for February 28, 2004 there will still be not “three full months”, but only 2, and for February 29 and March 1 (and beyond) you will have the desired “three months”

1 Like

I’ve just had a similar problem creating lookup tables for historical interest rates based on the number of months since a transaction to the present day. I first used DateDif based formulae, but the behaviour is awkward in the same way that others have noted, based on the internal treatment of month ends for the count.
I eventually found that DateDif is not documented in Excel, it is a legacy function, and there is no easy way to fix these issues that handles all the edge cases.
For my purposes, I found a workable solution using YEARFRAC as the basic element. This gives me an “exact” value from which I can derive a “number of months” integer as follows:
Months = ROUND(((YEARFRAC(start date,end date,1))*12),0)

Yearfrac has a 3rd parameter that changes the counting convention - 1 seems to be the most sensible value.
I used this to drive a lookup table with months going back over 20 years, and I found that it always gave me the correct lookup result - so I feel fairly confident that it will serve your purpose.

The other way to approach this for short periods of time would be to count days and then convert to months via a number-of-days-in-month lookup table, in which you can control how you want to handle part months etc. Looking at the detailed behaviour of the internal functions and finance industry discussions of the issues caused by different accountancy conventions is a real eye-opener!

hope this helps.