How to calculate PTO accrual in Google Sheets

Short answer: Divide the yearly PTO hours by your pay periods (80 hours ÷ 26 biweekly paychecks = 3.0769 hours a paycheck), count the paychecks since the employee started, and multiply. Keep the full decimals and subtract hours used. Handle the accrual cap with one row per pay period, because a single formula can't apply a cap correctly. At year end, carry over MIN(balance, carryover cap).

Formulas tested in a real spreadsheet · Google Sheets + Excel · Updated November 2026 · By Hazelshelf

Set up once

Put the policy on a Settings tab so no formula has a number typed into it: B1 PTO hours per year (80), B2 pay periods per year (weekly 52, biweekly 26, semi-monthly 24, monthly 12), B3 accrual cap in hours (120), B4 carryover cap in hours (40), and E2:E27 your pay dates for the year.

Step by step

1. Work out the rate per pay period

Don't round it: 4.62 × 26 is 120.12, not 120. This gives 3.0769 hours per paycheck for 80 hours paid biweekly. For hourly staff, use a rate per hour worked: =$B$1/2080 (0.0385 hours per hour).

=$B$1/$B$2

2. Count paychecks earned, including mid-year hires

Hire date in B6. A March 16 hire only counts the paydays from March 20, so pro-rating happens automatically.

=COUNTIFS($E$2:$E$27,"<="&TODAY(),$E$2:$E$27,">="&$B$6)

3. Calculate accrued hours and the balance

With the rate in B8 and paychecks in B9. Pull hours used from your leave log with =SUMIFS(Log!D:D, Log!A:A, A2, Log!E:E, "Approved").

Accrued:  =B9*B8
Balance:  =B10 + carryover - used
In days:  =balance/8

4. Apply the accrual cap with a ledger, one row per paycheck

Opening balance in D2, hours used in column B, then fill down. Accrual stops at 120 and restarts once time off pulls the balance below the cap. In our test the balance went 119.23 → 120 (only 0.77 credited), dropped to 104 after a 16-hour day off, and rose to 107.08 the next paycheck.

Credit (C3):   =MAX(0, MIN(Settings!$B$8, Settings!$B$3 - D2))
Balance (D3):  =D2 + C3 - B3
Lost to cap:   =Settings!$B$8 - C3

5. Carry over at year end

In the first row of the new year:

Opening balance: =MIN(Settings!$B$4, last_balance)
Forfeited:       =MAX(0, last_balance - Settings!$B$4)

Accrual cap vs carryover limit

	Accrual cap	Carryover limit
What it limits	The balance at any time	What moves into next year
When it applies	Every paycheck	Once, at the year reset
Hours lost?	None; accrual just pauses	Anything above the limit is forfeited
Formula	MIN(rate, cap - previous balance) per row	MIN(balance, carryover cap)

Why the one-cell version goes wrong

=MIN(cap, accrued) - used looks right, but it caps lifetime accrual, not the balance. Someone who sat at the cap and then took a day off snaps straight back to the cap, or never earns the hours back. It's fine as a quick check for a small team where nobody gets close to the cap. Anyone near the cap needs the ledger.

FAQ

How do I calculate PTO accrual per pay period?

Divide the annual PTO hours by the number of pay periods in the year. 80 hours on a biweekly payroll is 80 ÷ 26 = 3.0769 hours per paycheck, and 120 hours paid semi-monthly is 120 ÷ 24 = 5 hours.

How do I prorate PTO for someone who joins mid-year?

With per-paycheck accrual, count only the paydays on or after the hire date (COUNTIFS on your pay dates). For an upfront allowance, multiply by the months left: =80*(12-MONTH(hire)+1)/12 gives 66.7 hours for a March hire.

What's the difference between an accrual cap and a carryover limit?

A cap pauses earning while the balance is at the maximum, and no hours are lost. A carryover limit removes everything above the limit once a year, at the reset.

Can Google Sheets calculate a PTO balance with a cap automatically?

Yes. Use one row per paycheck with =MAX(0, MIN(rate, cap - previous balance)) as the credit. A single-cell MIN formula can't remember when someone hit the cap, so it gets the balance wrong after time off.

How do I calculate PTO per hour worked?

Divide the annual PTO hours by 2,080 (40 hours × 52 weeks). 80 hours a year is 0.0385 hours of PTO for every hour worked, so an 80-hour pay period earns about 3.08 hours.

Should I round the accrual rate?

No. Keep the full precision in the cell and only format the display to two decimals. Rounding to 3.08 adds up to 80.08 hours a year instead of 80.

More guides: How to track PTO in Google Sheets · Team leave calendar: avoid clashes