How to sum two date ranges using years months days

[EmmaGFunkytown]

Hi all,
I've done a bunch of searches to find a way to add two date ranges together and couldn't get anything to work. : (
I'm using Excel in 365.

I have employees with various amounts of time in service and I need to add these all together to get the complete amount.
Example: There are two or more date ranges that are in years months and days using:

=DATEDIF(D7,E7,"y")&" years "&DATEDIF(D7,E7,"ym")&" months "&DATEDIF(D7,E7,"md")&" days"
=DATEDIF(J7,K7,"y")&" years "&DATEDIF(J7,K7,"ym")&" months "&DATEDIF(J7,K7,"md")&" days"

How do I go about getting a year month day result for these combined?
Thank you SO much. : ) : ) : )

 

[ETAF]

I'm not 100% sure what you are wanting to sum

a sample sheet would help here

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

 

[Peter Bartholomew]

I suggest you should, if possible, use the original days rather than the intervals. That will give a 'cleaner' calculation that does not mean you have to deal with a total of "7 years 15 months and 54 days" (say). The following formula takes the second period of employment in days and appends it to the first end date before recalculating the interval.

= CONCAT(
    DATEDIF(firstStart, firstEnd + (1 + secondEnd - secondStart), {"y","ym","md"}) &
      {" years "," months "," days "}
  )

The calculation will never be precise to the day because the number of days in a month is not fixed, it depends upon which month one considers.

 

[BobBridges]

I'm not sure what Emma wants, either, but it sort of sounds like the goal is something like this: User X worked from 2023-04-21 to 2023-06-14 and then again from 2024-07-08 to 2024-07-19; that's a total of 65 days. Except a simple addition like that includes weekends in the total, which is probably not desired. Gotta figure out some way of omitting Sat and Sun, if those aren't work days.

 

[EmmaGFunkytown]

Hi all,
Thank you so much for the tip about using CONCAT! I am trying to sum the amounts of military time and civilian time in service to arrive at the total amount of time served for both. I'll give the above a try and see if I can figure out how to do it. thanks again!!!!!! :D