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")

On Fri, 8 Jul 2005 01:24:02 -0700, "Ambrosiy"
<[email protected]> 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