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.
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¡
Hi,
You may refer to my solution in this workbook.
Hope this helps.
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¡
Hi,
Check my result in row 12 of this workbook.
Hope this helps.
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.
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 ¡
You are welcome.
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.
This solution works...many thanks Ashish...
You are welcome.