Anniversary Date Increase based on Step

[JMcAnarney]

Group,

So I am doing salary forecasting, but I want to do it the most automated way possible. I am dealing with people on whose salary falls on matrix given a grade and step. That is the easy part by using a match and index formula. What is tricky to me is how can I write a formula for what step an employee is currently at. For example a person's salary will increase on their anniversary date, so it their aniversary date is 6/30 they will have one part of year where their payis x and one where their pay is y. What I think needs to be done is to write a long if statement to where if the current date is 03/03/2024 then we need to add anyone that has a hiring date post that date would get a step increase but anyone before would remain the same since their step already occurred.

Issues I am having is that their hiring date is set with a past year so it could be 02/02/2007; another issue is that i would need to factor in that the max a step could be is 20 and if the position is vacant it is automatically 1.

In picture above since that date is before the current date the year end step would be 5.

Can anyone give some guidance???

[#2]

@JMcAnarney 

In D2:

=IF(A2="", 1, MIN(C2+(DATE(YEAR(TODAY()), MONTH(A2), DAY(A2))>TODAY()), 20))

[#3]

@JMcAnarney 

You hint--and I do mean hint, as opposed to "describe in detail"--at a number of rules or guidelines here, operating in combination with one another. Length of time, length of time vis-a-vis a hire date and month, a max for step level increase, but also something about if the position is vacant then a 1....

It is next to impossible to write a formula that covers what is not understood. No, correction: it is impossible.

Please understand, I was (before retiring two decades ago) the director of the HR/Payroll database for a major US corporation, so I've dealt with salary systems and anniversary dates and so forth before. If I'm having trouble understanding what you've written, you can be pretty sure that most others will too.

It sounds as if you might have a spreadsheet already doing a small part of what you want to do. Could I ask that you post a copy of that (with no real names or other identifiable details; but with enough so we can understand what you've done so far)...post it on OneDrive or GoogleDrive with a link pasted here that grants access.

Then, along with the link that grants access, do your best to spell out in more details all of the factors that enter into salary increases based on steps and anniversary dates. Don't assume it's all obvious, no matter how clear it is to you.