The DATEDIF() bug

{17 Comments}

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.

RSS 17 Comments…

 Share your views
  1. 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¡

  2. 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¡

    • Hi,

      You are welcome. You may simply sum up the three individual formula driven columns to get total years, remaining month and remaining days.

      Hope this helps.

      • 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¡

  3. 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¡

  4. 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 ¡

  5. 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?

  6. Ioannis Matzavakis May 1, 2017 at 12:27 am

    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.

    • Thank you for pointing out that error. Could you share the correct formula please?

      • 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

Leave a Comment

Your email address will not be published.

*

*