Calculate Employee Salary Steps by Hire-Date Anniversary in WPS Spreadsheets
Question details
Calculate an employee's current salary step from the annual anniversary of the hire date, limit the result to step 20, and return step 1 for vacant positions without a hire date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet stores employee hire dates in column A and current salary steps in column C, and needs to calculate the applicable step for the current year.
- Observed behavior
- Vacant positions should automatically show step 1; filled positions should increase by one when the current year's hire-date anniversary has arrived, without exceeding step 20.
Confirm that hire dates are stored as real spreadsheet dates in column A and that current salary steps in column C are numeric. Decide whether the increase takes effect on the anniversary date or only after that date.
Use an anniversary formula that caps salary steps at 20
Use this formula when a blank hire date represents a vacant position and the salary step should increase on the employee's hire-date anniversary.
The formula returns 1 when A2 is blank. Otherwise, it adds one step when the current year's anniversary is today or has already passed, then uses MIN to prevent a result above 20.
In WPS Spreadsheets, click the cell where you want the calculated salary step to appear, such as D2.
Type or paste =IF(A2="",1,MIN(C2+(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<=TODAY()),20)) and press Enter. Replace A2 and C2 if your hire-date and current-step columns differ.
Select the formula cell and drag the fill handle down, or copy the cell and paste it into the remaining employee rows.
Test a row with an anniversary today, a future anniversary, a blank hire date, and a current step of 20 to verify that the results follow your salary-step policy.
Change the formula for increases that start after the anniversary
Use this variation when an employee should remain at the current step on the anniversary date and move up only on the following day.
Calculate Anniversary-Based Salary Steps with WPS Spreadsheets
WPS Spreadsheets lets you build an anniversary-based salary-step calculation with IF, MIN, DATE, YEAR, MONTH, DAY, and TODAY functions. Apply the formula to an entire employee list to identify vacant positions, add eligible annual steps, and enforce the maximum step automatically.
- 1. Open the employee worksheet: Open your payroll or HR workbook in WPS Spreadsheets and locate the hire date and current salary-step columns.
- 2. Create a result column: Insert or select a column for the calculated step, then click the first employee result cell, such as D2.
- 3. Add the anniversary formula: Enter =IF(A2="",1,MIN(C2+(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<=TODAY()),20)) and press Enter.
- 4. Apply the formula to all rows: Drag the fill handle from the formula cell down the result column to calculate steps for the full employee list.
Frequently Asked Questions
What does the formula return when the hire-date cell is blank?
The IF(A2="",1,...) portion returns 1 when A2 has no hire date, which can be used to identify a vacant position automatically.
How does the formula prevent a salary step above 20?
The MIN(...,20) function compares the calculated step with 20 and returns the lower value. An employee already at step 20 will remain at step 20.
How can I make the salary increase happen only after the anniversary date?
Change the comparison at the end of the anniversary test from <=TODAY() to <TODAY(). Use =IF(A2="",1,MIN(C2+(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY()),20)).
Why does my hire-date anniversary formula not calculate correctly?
Check that the hire-date cell contains a real date rather than text and that the current-step cell contains a number. In WPS Spreadsheets, select the cells and use Home > Number Format to review their formats.
How should I handle employees hired on February 29?
Test February 29 records because DATE(YEAR(TODAY()),2,29) may resolve to a March date in a non-leap year. Use the date your organization defines as the observed anniversary, such as February 28 or March 1.
