Payroll - Salary Step Increases

[JPav]

Hello, I'm in Payroll and need some help to automate our very manual process of increasing salaries. Our jobs have several steps (not all jobs have the same amount of steps) and they are linked to years of service but not all employees start at the same level when hired. For example, an employee might start as Occupation 1, Step 3 and then move up to Occupation 1, Step 4 after 1 year of service then Occupation 1, Step 5 after 2 years of service. Service for this first group I'm working on is based on 1 full year, so if you were hired April 18, 2023 your first Step increase would be April 18, 2024. Once at the top level employee remains in that level until termination or job change. To make it a little more complex, we also have % increases to each of the steps that are known in advance. For example, employee in Occupation 1, Step 3 can have a weekly rate of $1,000 on Dec 31, 2023 but with 1% salary increase on Jan 1, 2024 they move up to $1,010. All employees receive the same % of increase effective on the same date. Any help in figuring out how to structure this in Excel / formulas etc. would be greatly appreciated. Thank you!

 

[james_martin001]

I would start with a column with employee number, and another column with start date, column with pay scale ideally expressed as a single decimal value can be with one decimal place to show the step,

(dd/mm/yyyy as a minimum but in the format you like, due to advanced data manipulation I have to use yyyy-mm-dd)

If the pay scale progression is automatic, you can have a final column with the "current salary". To get to this column, don't be afraid to use helper columns before this whilst you workout the logic calculations. It is best to do this logic in two stages as you have an annual salary progression and a percentage increase on a different New Year date for all staff.

The main variables you have to work with are the start date and the difference to the current date, it is fairly simple to get the system current date via web searches.

You may have a problem if staff start in a Leap Year on 29/02/yyyy, so you have to logic check formulas can cope with this.

Start with several made up start dates and automate the formulas to show the correct pay rise at the next New Year, and the next Step increase. It's difficult to do the formulas in theory, I just do it without really thinking about it. There are so many ways to do this. Give it a go, maybe stop when stuck after 15 minutes, and look again the next day. I often make progress when coming back to a problem.

 

[JPav]

I would start with a column with employee number, and another column with start date, column with pay scale ideally expressed as a single decimal value can be with one decimal place to show the step,

(dd/mm/yyyy as a minimum but in the format you like, due to advanced data manipulation I have to use yyyy-mm-dd)

If the pay scale progression is automatic, you can have a final column with the "current salary". To get to this column, don't be afraid to use helper columns before this whilst you workout the logic calculations. It is best to do this logic in two stages as you have an annual salary progression and a percentage increase on a different New Year date for all staff.

The main variables you have to work with are the start date and the difference to the current date, it is fairly simple to get the system current date via web searches.

You may have a problem if staff start in a Leap Year on 29/02/yyyy, so you have to logic check formulas can cope with this.

Start with several made up start dates and automate the formulas to show the correct pay rise at the next New Year, and the next Step increase. It's difficult to do the formulas in theory, I just do it without really thinking about it. There are so many ways to do this. Give it a go, maybe stop when stuck after 15 minutes, and look again the next day. I often make progress when coming back to a problem.

Thank you so much for your response! I never though about a leap year start date. Thanks again! I'll give it a go.