Good Afternoon Everyone,
I recently took over for a Payroll Position at my current company, and we are about to update our PTO policy to go from a lump sum at the beginning of every year, to an accrual method. The rates of accrual have yet to be determined, but I wanted to get a start on creating an excel file to help keep track of the balances.
I've attached a screenshot of a very rough example, which would include the Accrual Rate, Week Worked, PTO Earned, PTO Taken and PTO balance. The PTO Earned would simply be =SUM(A1*B1), with A1 being the accrual rate and B1 being the week worked. I was going to enter in 1 for each week, to update the PTO Earned. As for the PTO Taken, I have a formula of =SUM(F1:Z1 - just an example) to total the number of hours taken. However, the PTO Balance is what I'm looking for help on.
I was trying to create a formula that would update if the PTO was taken. For example, if an employee hits their max PTO earned (56 for example), the MIN formula would prevent it from accruing anything more. However, when they take PTO and the balance is reduced, I was looking for help on a formula that would continue to accrue until the 56 hour max is met again.
I'm fairly new to excel and I've been going through some forums online trying to get this down, but I've run into a bit of a dead end.
Any help would be greatly appreciated!
