Anniversary Date Question - Excel

[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???

 

[awoohaw]

It may be easier for the forum if you supplied some example data of your grade and step matrix. Some examples of expected results would be helpful, too.
Mr. Excel has a tool called xl2bb add in (link below) that allows people to post mini workbooks. If you can't use that, then please post the data as a table and not an image. The less work the forum does in creating your scenario the more forum members will contribute to your question.

Thanks in advance.