Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why "datedif" function results sometimes negative numbers?
C26- 12/31/2004
C27- 3/1/2005 2 -2 =DATEDIF(C26,C27,"ym") =DATEDIF(C26,C27,"md") |
#2
|
|||
|
|||
On Fri, 8 Jul 2005 01:24:02 -0700, "Ambrosiy"
wrote: C26- 12/31/2004 C27- 3/1/2005 2 -2 =DATEDIF(C26,C27,"ym") =DATEDIF(C26,C27,"md") DATEDIF has a problem dealing with the end of month issues. I believe that when it is doing the "md" variation, it effectively adds the number of months (2) without adjusting for the EOM. It then subtracts the result from the EndDate. In Excel, =DATE(Year(C26), Month(C26)+2, Day(C26)) = 31 Feb 2005 which gets translated to 3 Mar 2005. C27 - "3 Mar 2005" gives you your -2 result. The problem, of course, is that months have different numbers of days. Depending on your specifications, it seems there are several ways of dealing with the issue. 1. If accuracy in elapsed time is required, use days or weeks. 2. Adjust for the end of month such that the partial month is related to the length of the preceding month, rather than the start month. This can lead to different intervals having the same result. 3. Count full calendar months and add on the partial months at the beginning and end. This can lead to a result such as 2 months 59 days. 4. Use the DATEDIFF function, but don't allow your start month to begin after the 28th. Here are different results using the different options; all with an ending date of 1 Mar 2005: Option 1 (days only) 12/28/2004 63 days 12/29/2004 62 days 12/30/2004 61 days 12/31/2004 60 days Option 2 (adjust for last EOM) 12/28/2004 2 months 1 day 12/29/2004 2 months 1 day 12/30/2004 2 months 1 day 12/31/2004 2 months 1 day Option 3 (Calendar Months) 12/28/2004 2 months 4 days 12/29/2004 2 months 3 days 12/30/2004 2 months 2 days 12/31/2004 2 months 1 day Option 4 (DateDif) 12/28/2004 2 months 1 day 12/29/2004 2 months 0 days 12/30/2004 2 months -1 days 12/31/2004 2 months -2 days The Calendar Months option (Option 3) will also give the following results with an EndDate of 29 Mar 2005: 12/1/2004 2 months 59 days 12/2/2004 2 months 58 days 12/3/2004 2 months 57 days 12/4/2004 2 months 56 days So which convention you wish to use is up to you, and dependent on the requirements of your task. Hope this helps. I have VBA routines for options 2 and 3. Option 1 is simple subtraction; and option four combines DATEDIF with appropriate strings to get the result I posted. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Using function results as parameters in another function | Excel Worksheet Functions | |||
Why do I get a #Name error when I use the DateDif function? | Excel Worksheet Functions | |||
Does Excel 2000 have a 'datedif' function to calculate the number. | Excel Worksheet Functions |