How to calculate PTO accrual
I am trying to write a formula that calculates an employees PTO balance.
For example, the employee can accrue up to a maximum of 100 hours. Additional amounts are accrued each day regardless if the day is worked, PTO, or weekend.
The employee stops accruing once the maximum is reached but should start accruing again when they drop below their 100 hour threshold.
I have written, (100/365*(current date+1-hire date)) to calculate the amount accrued.
I then added a MIN formula to return either the calculated accrual less hours used or the maximum accrual-able hours.
However if the accrual far exceeds the maximum and the employee takes time off the formula still returns the 100 hour maximum. (i.e. 120 hours accrued then takes 8 hours, 100 hours is returned instead of 92). If I subtract hours used from the MIN formula above the it reduces the maximum accrual to 92.
Essentially, I need to write something that restarts the accrual once enough hours are used to drop below the maximum threshold but only for hours accrued after the maximum is reached. (i.e. 100 hours accrued as of January, then 8 hours used in March, additional accrued hours should only add back to the balance after use of PTO in March, not all the hours that would have been accrued if they were under the cap in February)
[link] [comments]
Want to read more?
Check out the full article on the original site