Forum Discussion
Service time
When using =DATEDIF(AA22,AB22,"Y")& " YEARS, "&DATEDIF(AA22,AB22,"YM")&" MONTHS, "&DATEDIF(AA22,AB22,"MD")&" DAYS, " to calculate sevice time, I have people who return, there new time is calculated using =DATEDIF(AC22,TODAY(),"Y")& " YEARS, "&DATEDIF(AC22,TODAY(),"YM")&" MONTHS, "&DATEDIF(AC22,TODAY(),"MD")&" DAYS, "
Is there a way to combine the two to get a total time?
4 Replies
You can combine the two formulas to get the total service time. You can use the DATEDIF function to calculate the difference between the start date (entry date) and the end date (either the current date or a specific return date). Here is an example of how you can do it:
Assuming AA22 and AB22 contain the entry date and return date respectively, and AC22 contains the new entry date, you can combine the formulas like this:
=DATEDIF(AA22, AB22, "Y") & " YEARS, " & DATEDIF(AA22, AB22, "YM") & " MONTHS, " & DATEDIF(AA22, AB22, "MD") & " DAYS, " & DATEDIF(AB22, TODAY(), "Y") & " YEARS, " & DATEDIF(AB22, TODAY(), "YM") & " MONTHS, " & DATEDIF(AB22, TODAY(), "MD") & " DAYS"
This formula calculates the service time from AA22 to AB22 and then adds the service time from AB22 to the current date (TODAY()). The result is a combined string showing the total service time.
Make sure to adjust cell references based on your actual data.
If you use Excel 365, you can use the TEXTJOIN function along with DATEDIF to combine the two sets of calculations. Here is a example how you can do it:
=TEXTJOIN(", ", TRUE, DATEDIF(AA22, AB22, "Y") & " YEARS", DATEDIF(AA22, AB22, "YM") & " MONTHS", DATEDIF(AA22, AB22, "MD") & " DAYS", DATEDIF(AB22, TODAY(), "Y") & " YEARS", DATEDIF(AB22, TODAY(), "YM") & " MONTHS", DATEDIF(AB22, TODAY(), "MD") & " DAYS")
This formula uses TEXTJOIN to concatenate the different components with commas and spaces. It concatenates the service time from AA22 to AB22 and the service time from AB22 to the current date. The text, steps and functions were created with the help of AI.
My answers are voluntary and without guarantee!
Hope I was able to help you with this information.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- My AA22 is original start, AB22 is original leave date. AC22 is 2nd start date, AD22 is 2nd leave date
hyview I assume you want the total number of years, months and days rather than a long text string that merely returns the years, months and days for each period.
To do that you could do something like this (Excel 365 or 2021):
=LET( y_1, DATEDIF(AA22,AB22,"Y"), m_1, DATEDIF(AA22,AB22,"YM"), d_1, DATEDIF(AA22,AB22,"MD"), y_2, DATEDIF(AC22,AD22,"Y"), m_2, DATEDIF(AC22,AD22,"YM"), d_2, DATEDIF(AC22,AD22,"MD"), TEXTJOIN(" ",,y_1+y_2, "Years",m_1+m_2,"Months",d_1+d_2,"Days"))But, that could leave you with a result like this:
Obviously, that's nonsense as 16 months by itself translates to 1 year and 4 months. And 42 days equals 1 month plus 14, 13, 12 or 11 days, So you would want it to show 2 years, 5 months and 11 to 14 days, depending on which months of which years were covered. Probably doable but the formula would become a fair bit longer.
Why not just use this?
=AB22-AA22+AD22-AC22 or =(AB22-AA22+AD22-AC22)/365.25This will return 892 for the total number of days or 2.44 (rounded to 2 decimals) years. The latter being 2 years and just over 5 months (i.e 0.44 x 12 = 5.28).
