The DATEDIF() bug

{ 17 Comments }

By Ashish Mathur · · · Leave a comment

Assume a beginning date in cell A7 and ending date in cell B7.  In range C7:E7, the task is to compute the years (in cell C7), remaining months (in cell D7) and remaining days (in cell E7).  As an example, if one types in 12-March-2013 in cell A7 and 24-March-2014 in cell B7, then the result in range C7:E7 should be 1,12 and 12 respectively.  The formulas for getting these results are:

1.  In cell C7 – =DATEDIF($A7,$B7,”y”)
2.  In cell D7 – =DATEDIF($A7,$B7,”ym”)
3.  In cell E7 – =DATEDIF($A7,$B7,”md”)

So far so good.  All the results are correct.  However, there is a problem with the “md” syntax of the DATEDIF() function.  Try this

1. In cell A7, enter 31-July-2007
2. In cell B7, enter 02-March-2009
3. In cell C7, enter =DATEDIF($A7,$B7,”y”)
4. In cell D7, enter =DATEDIF($A7,$B7,”ym”)
5. In cell E7, enter =DATEDIF($A7,$B7,”md”)

The result in cell E7 will be -1 which is clearly incorrect.  To get the correct result of 2 in cell E7, try this formula instead

=B7-EDATE(A7,(C7*12)+D7)

Hope this helps.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • So, I’d like a way to ge Excel to return these results for these periods:
    11-12-2003 / 14-09-2004 = 5 days 9 months 0 years
    08-05-2008 / 02-06-2008 = 26 days 0 months 0 years
    15-09-08 / 14-09-2010 = 0 days 0 months 2 years

    and so. Is it posible? Thanks¡

    Reply

  • HI, thanks¡ Yes, that workbook definitely gets near to my ideal solution.
    And now what I’d need is to sum all the periods with a formula that returns (again) not the total days and total months but the total years, remaining months and remaining days.
    Is it somehow posible?
    Regards¡

    Reply

      • Excuse me, but I don’t quite understand:

        The periods I am working with are:
        11-12-2003/14-09-2004—>0 years, 9 months, 4 days
        08-05-2008/02-06-2008—->0 years, 0 months, 26 days
        15-09-2008/14-09-2010—>2 years, 0 months, 0 days
        24-09-2010/11-03-2011–>0 years, 5 months, 16 days
        21-03-2011/15-04-2011–>0 years, 0 months, 26 days
        02-05-2011/14-09-2011–>0 years, 4 months, 13 days

        If I sum up the periods I am working right now in the sheet, then I get: 2 years, 18 months, 85 days.
        But I need a formula for the TOTAL which should return in this case, something like 3 years, 8 months and 28 days.

        Please any help? I just can’t figure out myself 🙁
        Thanks so much¡

        Reply

  • Hi¡
    Yes, you did as far as years and months are concerned, but… what about days?
    With the periods in your workbook we get 8 years, 8 months (so far so good) but 60 DAYS¡

    of course, 60 days are 2 months, so: how can I get that the formula counts (for the example in your workbook again) 8 years, 9 months and 30 days?

    thanks so much once more¡

    Reply

  • HI,

    I think this is it¡ I have no time today to look at it thoroughly either, but at first sight, it seems to be the formula(s) I needed.

    I’ll let you know if any lapses found, but definitely this approaches to the requested formula(s).

    Thanks again ¡

    Reply

  • Question: I want to calculate the years of service of employees as at the 31st Oct 2013.
    Employee 1 started on the 7 Sep 1984. Using the date formula, =DATE(2013,10,31-1984-9-7)/365.25, I get the correct answer of 29.1 years. Employee 2 started on the 12 Dec 1989…so using the formula =DATE(2013,10,31-1989-12-12)/365.25, I get the answer of 203.5 years…clearly, this answer is wrong. Why does it calculate incorrectly? It seems to do this only for years >1988. Why?

    Reply

  • This formula is not correct. If the ending date is 1/3/2009 then for both starting dates 30/7/2007 and 31/7/2007, the result is the same: 1 year, 7 months, 1 day. The correct formula is much more complicated.

    Reply

      • Hi, i use this formula for a while, not sure if it’s working as intended but hope it helps

        years :
        =DATEDIF($U$2,$V$2,”y”)

        months :
        =IF(DATEDIF($U$2,$V$2,”md”)>=0,DATEDIF($U$2,$V$2,”ym”),DATEDIF($U$2,$V$2,”ym”)-1)

        days
        =IF(DATEDIF($U$2,$V$2,”md”)>=0,DATEDIF($U$2,$V$2,”md”),DATEDIF($U$2,$V$2,”md”)+31)

        using this formula i tested from 2/28/2008 to 3/1/2012 will give 4 years 0 month 2 days

        Reply