Thread: Adding Series of Years, Months, & Days

Hi,

I'm trying to add different series of years, months, and days together to obtain display a grand total like the one I have in bold below:

Example:

1 years, 10 months, 30 days
+
10 years, 6 months, 28 days
+
1 years, 5 months, 29 days

Intermediate Total = 12 years, 21 months, 87 days

Display Goal Total = 14 years, 11 months, 27 days*

*Note: Assuming 30 days = 1 month

Any help with this one is most appreciated!
Jay

How did you get your data? If they are the result of calculating the difference between 2 dates then it would be easier to use the base data, e.g.

If you have start dates in A2:A10 and end dates in B2:B10 and you want the total time in the format given then you can use a formula like

=DATEDIF(SUM(A2:A10),SUM(B2:B10),"y")&" years, "&DATEDIF(SUM(A2:A10),SUM(B2:B10),"ym")&" months, "&DATEDIF(SUM(A2:A10),SUM(B2:B10),"md")&" days "

Hi All,

I am in the military and have to track periods of service member's periods of service. Some of those members have "broken time", so their dates are not consecutive. Therefore, I need to add up each period of "good time" and then come up with an accurate grand total for the total "good time" they have served, which needs to be broken down by years, months, and days.

So, I am subtracting two dates to determine each period of good service.

While it is a bit clunky, I have been using the following formula to calculate and the display the difference between dates:

=YEAR(C72)-YEAR(B72)-IF(OR(MONTH(C72)<MONTH(B72),AND(MONTH(C72)=MONTH(B72), DAY(C72)<DAY(B72))),1,0)&" years, "&MONTH(C72)-MONTH(B72)+IF(AND(MONTH(C72) <=MONTH(B72),DAY(C72)<DAY(B72)),11,IF(AND(MONTH(C72)<MONTH(B72),DAY(C72) >=DAY(B72)),12,IF(AND(MONTH(C72)>MONTH(B72),DAY(C72)<DAY(B72)),-1)))&" months, "&C72-DATE(YEAR(C72),MONTH(C72)-IF(DAY(C72)<DAY(B72),1,0),DAY(B72))&" days"

For instance, if I am calculating the "good time" for this date range:

28-Aug-1981 TO 27-Mar-1992, the formula I have been using yields:

10 years, 6 months, 28 days

A member with "broken time" might have 2 or 3 periods of "good time" like the above. So after I obtain each "good time" period, which I display in columns, I then want to add all the periods of "good time" together to obtain a grand total of a member's "good time".

Haven't had a chance yet to try out the .zip file!

Thanks,
Jay

Trying to add text strings like "2 years, 3 months, 4 days" is unnecessarily complicated. Try using your base dates to calculate the total period as per my suggestion above.

Thank you for your help fellas!

I just had surgery, so will give your suggestions a go once I am feeling better and my mind is more clear!

Again, thank you and my apologies for not answering a bit quicker--I'll try all your suggestions in a couple of days and let you know I how I do.

With best wishes,
Jay

Daddylonglegs,

I just gave your solution a run and it worked like a champ! Many thanks for your help.

Likewise, thanks to everyone else who also lent a hand (formula)!

Jay

Originally Posted by daddylonglegs

Trying to add text strings like "2 years, 3 months, 4 days" is unnecessarily complicated. Try using your base dates to calculate the total period as per my suggestion above.

Hello!
I know this post has been made years ago but i still hope for a reply. I tried your formula on a data which has two date ranges and it worked. Now i do not know how to do it with a data that has three or more date ranges. I tried adding another SUM command for each of the year, months and days (ex.=DATEDIF(Sum(A2,A4),SUM(B2,B4),SUM(C2,C4),"y")&"years",...) but Excel returns an error message about the formula being too long or there are too many entries.
Hope you could help me.
Thanks,
Eds