How to Calculate Total Employment Time with Rehire Dates in Excel
Question details
Calculate an employee's combined years and months of service when they have separate employment periods due to being terminated and later rehired.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- HR professionals or managers need to determine the total tenure of employees who have left the company and returned, combining multiple date ranges into a single 'Years and Months' value.
- Observed behavior
- The user needs a formula to sum the durations of two separate employment periods without counting the gap between termination and rehire.
Ensure that your hire, termination, and rehire dates are properly formatted as Date values in your spreadsheet, and not as plain text.
Use QUOTIENT and MOD for Approximate Total Time
Calculate the total combined days across both employment periods and convert them into years and months using a single formula.
This method calculates the sum of the days from both the initial employment period and the rehire period. It divides the total days by 365.25 (to account for leap years) to find the years, and uses the remainder to find the months.
Ensure your dates are in specific cells. For example: First Hire Date in E3, First Termination Date in F3, Rehire Date in K3, and Current Date (or Final Termination) in L2.
Select the cell where you want the result to appear and enter the formula: =QUOTIENT(F3-E3+$L$2-K3,365.25)&" Y ""IENT(MOD(F3-E3+$L$2-K3,365.25),365.25/12)&" M"
Press Enter. The cell will now display the combined tenure in a format like '3 Y 4 M' (3 Years and 4 Months). Adjust the cell references as needed for your specific worksheet layout.
Calculate Exact Calendar Duration Using DATEDIF
Use the DATEDIF function to calculate the exact years and months for each employment period separately, then combine them.
Calculate Complex Employment Histories Effortlessly in WPS Spreadsheet
WPS Spreadsheet features full support for advanced date calculations, making it simple to manage HR records, calculate tenure, and manipulate date data with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your employee records document.
- 2. Format Your Date Cells: Highlight your date columns, right-click, select 'Format Cells', and ensure they are set to Date format.
- 3. Enter the Formula: Click the target cell and paste the provided QUOTIENT formula to combine employment periods.
- 4. Batch Process Employees: Drag the fill handle down from your formula cell to instantly calculate total employment time for your entire staff roster.
Frequently Asked Questions
Why is my formula returning a '#VALUE!' error?
This error typically occurs when one or more of the referenced cells contain text instead of a valid date, or if there are invisible spaces in the cell. Check your hire and termination dates to ensure they are properly formatted as dates.
Can I calculate total tenure if an employee has been rehired multiple times?
Yes. To handle more than two employment periods, simply add the difference (End Date - Start Date) of each additional employment block to the total sum of days before using the QUOTIENT division.
Does WPS Spreadsheet support the DATEDIF function?
Yes, WPS Spreadsheet fully supports the DATEDIF function for calculating the exact difference between two dates in days, months, or years, making it fully compatible with Excel HR templates.
