How to Add Years and Months from Multiple Service Periods in Excel
Question details
The user needs to calculate the total employee service time across separate employment date ranges, combining the total years and months accurately while carrying over any extra months into additional years.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating cumulative employee tenure or service time from disjointed start and end dates.
- Observed behavior
- The goal is to output a single text string representing the combined duration of multiple separate service periods, such as '26 year(s), 1 month(s)'.
Verify that your start and end dates for each service period are formatted as valid Excel dates, not as text strings. Ensure you have clear cell references (e.g., A2/B2 for the first period and A3/B3 for the second period) before applying the formulas.
Use DATEDIF with QUOTIENT and MOD Functions
This comprehensive formula calculates the exact years and months for each period independently, adds them together, and converts any accumulated months over 12 into additional years.
The DATEDIF function calculates the difference between dates. By combining it with QUOTIENT (to extract full years from the accumulated months) and MOD (to find the remaining months after dividing by 12), you ensure the final text string is mathematically accurate.
Click on the blank cell where you want the combined total service time to be displayed.
Type the following formula: =DATEDIF(A2,B2,"y")+DATEDIF(A3,B3,"y")+QUOTIENT(DATEDIF(A2,B2,"ym")+DATEDIF(A3,B3,"ym"),12)&" year(s), "&MOD(DATEDIF(A2,B2,"ym")+DATEDIF(A3,B3,"ym"),12)&" month(s)"
Replace A2 and B2 with the start and end dates of the first period. Replace A3 and B3 with the start and end dates of the second period.
Press Enter to execute the formula and view the total combined years and months.
Use a Simplified DATEDIF Date Subtraction Formula
A shorter, alternative approach that mathematically combines the dates directly within the DATEDIF function arguments before generating the final text string.
Calculate Service Periods Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date functions like DATEDIF, QUOTIENT, and MOD out of the box. You can execute these complex employee tenure calculations identically to Excel, making it a powerful and free tool for HR and data analysis tasks.
- 1. Open your date records: Launch WPS Spreadsheet and open the file containing your employee service dates.
- 2. Select the target cell: Click on the cell where you want the combined years and months to appear.
- 3. Apply the DATEDIF formula: Paste your combined DATEDIF formula into the formula bar and press Enter.
- 4. Fill down for multiple employees: Drag the fill handle at the bottom right of the cell downwards to apply the calculation to other rows instantly.
Frequently Asked Questions
Why does my DATEDIF formula return a #NUM! error?
This error typically occurs if the start date is chronologically later than the end date. Check your cell references to ensure the older date is the first argument in the DATEDIF function.
Can I also calculate the remaining days for the service periods?
Yes. You can append the remaining days by adding another section to your formula using the "md" argument. For example: &" "&DATEDIF(A2,B2,"md")&" day(s)". Note that combining days across multiple periods requires similar MOD and QUOTIENT adjustments for 30-day averages.
Is the DATEDIF function fully supported in WPS Office?
Yes, WPS Spreadsheet fully supports the DATEDIF function. You can use it to calculate the days, months, or years between two dates seamlessly, ensuring complete compatibility with your existing Excel files.
