I am building an app to help bus operators track their time and pay rates.
The rules for raises are, step1, 70% of the Toprate for the first year, step 2, 80% for the next year, step 3, 90% for the next 6 months, step 4, 95% for the next 6 months, then step 5, full pay. The start date is different for everybody, and some users are already partially through the raises. I have a separate column for what their current step is 1, 2, 3, 4, 5. I would like to fill in the rest of the dates so that their pay for each day is paid based on if the date for the work is within each step date.
How does your top rate change? Or is it fixed?
The top rate changes year to year based on our contract. For example, there is a new top rate coming that is a 4% raise on the previous top rate. More to my point is determining the step raises from the user’s current rate and their step entry date. The formula for the steps is described above.
If the users pay step is 1 then step 2 is 1 year later, step 3 another year later, step 4 six months later and finally step 5 six months later.
I would like the dates to be dynamic so I can use them in my work table that has the users work by date and time.
For the Steps Step 1 is 70% of the top rate, 2 is 80%, 3 is 90%, 4 is 95% and finally 5 is 100%
I would like for the work data to have a separate rate number and top rate so that if they work one day at step 2 and the next day get step 3, I don’t want all the previous work to suddenly be calculated at step 3.
So circling back to your original post, you have a start date for everybody, and now you just need to calculate the dates where next steps start?
Adapting this formula from @Jeff_Hager .
((Now-DAY(Now)+15)+(Years*365.25))
-
DAY((Now-DAY(Now)+15)+(Years*365.25))
+
DAY(Now)
With Now being the Start Date, Years being:
1 for Step 2
2 for Step 3
2.5 for Step 4
3 for Step 5
2 Likes