Calculate Employee Tenure Between Start and End Dates in WPS Office Spreadsheet
Question details
The user wants to calculate the number of years between an employee's start date and end date in WPS Office Spreadsheet, with the formula automatically using today's date when the end-date cell is blank.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- An employee roster has start dates and optional end dates, and the user needs a tenure column that remains accurate for current employees.
- Observed behavior
- The desired calculation is a decimal year value that updates automatically when the end date is absent.
Make sure the start-date and end-date columns contain real date values rather than text, and identify the first row where you want the tenure formula to be entered.
Use IF and YEARFRAC to calculate tenure when the end date is blank
This is the recommended approach because it is compatible with WPS Office Spreadsheet, Microsoft Excel, and most spreadsheet versions.
The YEARFRAC function returns the fractional number of years between two dates. Wrapping it in IF lets the formula choose TODAY() when no end date exists.
Launch WPS Office and open the employee workbook that contains the start and end dates.
Click the first cell where you want the tenure result to appear, for example C2.
Type =IF(B2="",YEARFRAC(A2,TODAY(),1),YEARFRAC(A2,B2,1)) and adjust A2 to the start-date cell and B2 to the end-date cell.
Press Enter, then drag the fill handle down or double-click it to apply the formula to the remaining employees.
Select the result cells, right-click, choose Format Cells, click Number, and set the desired number of decimal places.
Use LET to make the formula easier to read and avoid repeating the end-date check
Use this if your WPS Office version supports LET and you prefer a named variable inside the formula.
Calculate Employee Tenure Automatically in WPS Office Spreadsheet
WPS Office Spreadsheet can perform the tenure calculation directly with Excel-compatible functions. Use IF, YEARFRAC, and TODAY to return decimal years and automatically handle blank end dates, then fill the formula down the employee list.
- 1. Download and install WPS Office: Go to the official WPS Office website, download the free installer, and complete the installation.
- 2. Open your Excel file in WPS Spreadsheet: Launch WPS Office, click Open, and select the employee workbook containing start and end dates.
- 3. Enter the tenure formula: Click the result cell and enter =IF(B2="",YEARFRAC(A2,TODAY(),1),YEARFRAC(A2,B2,1)) using your actual date cells.
- 4. Fill the formula down: Press Enter, then drag the fill handle down to calculate tenure for all employees.
- 5. Format the results: Select the calculated cells, right-click, choose Format Cells, and set the number of decimal places required.
Frequently Asked Questions
Can I show tenure in years and months instead of a decimal year value?
Yes. You can use a formula such as =IF(B2="",DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")&" months",DATEDIF(A2,B2,"Y")&" years "&DATEDIF(A2,B2,"YM")&" months"). This displays whole years and remaining months for each employee.
What does the 1 at the end of YEARFRAC do?
The third argument of YEARFRAC sets the day-count basis. A value of 1 uses the actual number of days in each month and year, which is suitable for service or tenure calculations.
Will the formula update automatically when I open the file later?
Yes. The TODAY() function is volatile, so WPS Office Spreadsheet recalculates it when the workbook opens or when the workbook is recalculated, keeping blank-end-date tenure values current.
Why do I get a #VALUE! error from the tenure formula?
A #VALUE! error usually means one of the referenced date cells contains text, is empty in a required position, or is not formatted as a valid date. Verify that start and end date cells contain real date values.
Will this formula also work if I open the workbook in Microsoft Excel?
Yes. The IF, YEARFRAC, and TODAY functions are standard spreadsheet functions, so the formula is compatible with Microsoft Excel and WPS Office Spreadsheet.
