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:

Example:

  • =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”
=MONTHS(DATE(2023;12;1);DATE(2024;2;29)+1;1)

1 Like