Many plans earn paid time off as a rate per hour worked — e.g. one hour of PTO for every 30 hours. Multiply hours worked by the accrual rate and round to a sensible increment.
The example
1 hour PTO per 30 worked.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Hours worked | 120 |
| 3 | Rate (1/30) | → 4.00 hrs |
The formula
The formula:
How it works
How it works:
- Set the accrual rate as PTO hours per hour worked — 1/30 = 0.0333, or use your plan’s figure.
- Multiply hours worked in the period by that rate.
- Round to your plan’s increment (two decimals, or
MROUND(…, 0.25)for quarter-hours). - Add to a running balance, and subtract time taken, to track the current PTO available.
Cap the balance: many plans limit accrual to a maximum. Wrap the running balance in MIN(balance, cap) so it stops growing once the cap is reached, and use MAX(balance, 0) so it never goes negative when time is used.
Try it: interactive demo
Hours worked and accrual rate.
Variations
Flat per pay period
Fixed accrual:
Round to quarter-hour
0.25 increments:
Capped balance
Stop at the max:
Pitfalls & errors
Rate direction. “1 per 30 hours” is hours÷30 (rate 0.0333), not ×30.
Cap and floor. Use MIN for the accrual cap and MAX(…,0) so balances never go negative.
Match the increment. Round to whatever unit your payroll system tracks.
Practice workbook
Frequently asked questions
How do I calculate PTO accrual in Excel?
How do I cap the PTO balance?
How do I accrue a flat amount per pay period?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class