Forum Discussion
Service time
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).
If you want to calculate the total service time considering both periods (original start to original leave date and 2nd start date to 2nd leave date), you can adjust the formula accordingly. Assuming AA22 is the original start date, AB22 is the original leave date, AC22 is the 2nd start date, and AD22 is the 2nd leave date, you can use the following formula:
=TEXTJOIN(", ", TRUE,
DATEDIF(AA22, AB22, "Y") & " YEARS",
DATEDIF(AA22, AB22, "YM") & " MONTHS",
DATEDIF(AA22, AB22, "MD") & " DAYS",
DATEDIF(AC22, AD22, "Y") & " YEARS",
DATEDIF(AC22, AD22, "YM") & " MONTHS",
DATEDIF(AC22, AD22, "MD") & " DAYS"
)This formula calculates the service time for the original period (AA22 to AB22) and the service time for the 2nd period (AC22 to AD22) and then combines them into a single string with commas and spaces using the TEXTJOIN function.
Make sure to adjust the cell references based on your actual data in your worksheet.
