Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Datedif gives wrong result
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Datedif gives wrong result
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 "Jonathan Cooper" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Datedif gives wrong result
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. -- David Biddulph Jonathan Cooper wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displays wrong result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
My Datedif function only returns 0's in the cell what's wrong? | Excel Worksheet Functions |