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.

• aNON says:

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¡

• aNon says:

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.

• aNOn says:

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¡

• aNon says:

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¡

• Hi,

You may refer to my solution in this workbook.

I have not checked the result thoroughly so please let me know of any lapses.

• aNon says:

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 ¡

• Abdullah Kajee says:

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?

• Hi,

Your formula is wrong. Try this

1. In cell A5, type 7/9/1984 i.e. 7 September 1984
2. In cell A6, type 12/12/1989
3. In cell B5, enter this formula and copy down

=(DATE(2013,10,31)-A5)/365.25

Hope this helps.

• Abdullah Kajee says:

This solution works…many thanks Ashish…

• Ioannis Matzavakis says:

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?

• Charlie says:

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