Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ambrosiy
 
Posts: n/a
Default 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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Using function results as parameters in another function Steve Haack Excel Worksheet Functions 1 April 24th 05 11:39 PM
Why do I get a #Name error when I use the DateDif function? PhilS Excel Worksheet Functions 9 April 5th 05 04:51 PM
Does Excel 2000 have a 'datedif' function to calculate the number. Kaddy Excel Worksheet Functions 7 December 11th 04 09:53 PM


All times are GMT +1. The time now is 02:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"