I am using an if statement to do the following for accrual of sick time.
We have the following columns:
Hours worked
Current period accrual based on hours worked
Annual accrual which is limited to 40 hours
Sick time used
Accrual remaining after sick time used.
If the total accrued sick time reaches 40 hours for the year, then the result of the current accrual needs to be limited so that the total accrual does not exceed 40 for the year. In the total annual accrual for the year, if the total is 40 or more, then the result we're getting back is 40. That column is working fine. In the column for the current pay period accrual, I want it to say.....if the annual total is less than 40, then take the # of hours times the accrual rate, but I want that to limit itself so that the annual total will not exceed 40. So, let's say the annual total is 38 and the current period accrual would be 3 but we want to limit it to 2 because that's what will bring us to the maximum annual accrual of 40. Is there a way to limit the current period calculation so that the annual sum won't exceed the max? In other words, we would be referencing the annual accrual column and if it's less than 40, we will go ahead and accrue the current period accrual, but we also want to test the sum of the annual accrual plus the current accrual and if it's greater than 40, we want to only accrue the amount that will bring us up to 40.
Sounds confusing as I write this, but hopefully it makes sense!
Thanks so much for any help!
Bookmarks