Datedif gives wrong result

[Jonathan Cooper]

Beginning Date Ending Date months
11/30/2009 12/31/2009 1
11/30/2009 1/31/2010 2
11/30/2009 2/28/2010 2
11/30/2009 3/31/2010 4

I'm a bit puzzled. Beginning Date is in A1. Ending date in B1 and my
datedif formula in column C.

The formula starting in C2 is =DATEDIF(A2,B2,"m"). Shouldn't February be
equal to 3? Based upon Chip's site, I'm guessing that it has something to do
with leap year but the last one was in 2008 and the next one is in 2012 so
neither Date1 or Date2 have a leap year.

I've tried different beginning dates but January and February give the same
result. Obviously it is something to do with February.

Unfortunately I'm using Excel 2003 SP3. Windows XP SP3.

Any other way to find the number of months inbetween two dates?

 

[מיכ×ל (מיקי) ×בידן]

As far as I remember [being among those who used "Lotus 123"] it was a "bug"
that Excel - who was the follow-up of "Lotus 123" - carried on with.
Unless you get better suggestion than mine - switch to "1904 system date"
[Tools > Options > Calculate tab]
Micky

 

[David Biddulph]

Nothing to do specifically with February or with leap years.

You need to think about what DATEDIF is doing.
If your beginning date is the nth of one month, DATEDIF(...,...,"m") will
increment when the end date reaches the nth of each succeeding month.
Hence if the start date is the 30th, DATEDIF will increment at the 30th of
each subsequent month. Obviously February does not have a 30th, so DATEDIF
doesn't increment until the end date steps into March.
You will see the same thing if your start date is 31st. See what happens if
start date is 31st Oct, and end date is the last day of each subsequent
month. It won't increment DATEDIF if last day of end month is 30th, so you
have to wait until you go on to following month.