WPS Spreadsheet: Calculate Employee Years Using an Inactive Date
Question details
The user needs to calculate an employee’s length of service using the inactive date when one exists and the current date for active employees.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The hire date is stored in G2 and the employee’s inactive date is stored in H2. H2 may be blank for active employees.
- Observed behavior
- The calculation must use H2 when it contains a date and TODAY() when H2 is blank, with results available as decimal years or as years, months, and days.
Confirm that G2 and H2 contain valid spreadsheet dates, not text strings. Leave H2 blank for active employees so the formula can automatically use TODAY().
Calculate decimal years from the inactive or current date
Use this formula when you need a numeric service duration that can be used in calculations, sorting, or reports.
Open the worksheet in WPS Spreadsheet and select the cell where the employee’s service years should appear.
Type =DAYS(IF(H2="",TODAY(),H2),G2)/365 into the selected cell, then press Enter. The formula uses H2 when an inactive date exists and TODAY() when H2 is blank.
With the result cell selected, open the Home tab and use the Number format controls to display the value as a number with the required decimal places.
Display years, months, and days as a detailed service period
Use this option when the report should show a readable breakdown instead of a decimal year value.
Calculate Active and Inactive Employee Service Automatically in WPS Spreadsheet
WPS Spreadsheet lets you combine IF, TODAY, DAYS, and DATEDIF to calculate service time from either an employee’s inactive date or the current date. Build the formula once, fill it down, and keep the result compatible with common Excel workbooks.
- 1. Open the employee worksheet: Launch WPS Office, open the workbook in WPS Spreadsheet, and locate the hire-date column and inactive-date column.
- 2. Add the decimal-years formula: Select the output cell and enter =DAYS(IF(H2="",TODAY(),H2),G2)/365, then press Enter.
- 3. Add a detailed service formula if needed: For a text breakdown, select another output cell and enter the DATEDIF formula using G2 as the hire date and IF(H2="",TODAY(),H2) as the end date.
- 4. Apply the formulas to the table: Drag the fill handle down the output column so each employee row calculates from its own hire and inactive dates.
Frequently Asked Questions
What formula uses the inactive date only when it is available?
Use =DAYS(IF(H2="",TODAY(),H2),G2)/365. The IF function checks whether H2 is blank; it uses TODAY() for active employees and H2 for employees with an inactive date.
How can I show service time as years, months, and days?
Enter =DATEDIF(G2,IF(H2="",TODAY(),H2),"Y")&" Years, "&DATEDIF(G2,IF(H2="",TODAY(),H2),"YM")&" Months, "&DATEDIF(G2,IF(H2="",TODAY(),H2),"MD")&" Days" in the result cell.
Why does the employee service formula return an error?
Check that G2 and H2 contain valid dates and that the hire date is not later than the inactive date or today. Text that looks like a date may need to be converted using the Date format or WPS Spreadsheet’s data conversion tools.
Will active employee results update automatically?
Yes. When H2 is blank, TODAY() supplies the end date, so the result changes when the workbook recalculates on a later day. Employees with a date in H2 keep using that fixed inactive date.
Can I copy this formula to the rest of the employee list?
Yes. Enter the formula in the first result row, select the cell, and drag its fill handle down. Relative references such as G2 and H2 will change to match each employee’s row.
