How to calculate PTO accrual (5 methods, with spreadsheet formulas)

Updated October 2026 · About 8 minutes to read

"Accrual" means time off is earned gradually instead of handed out all at once. There's no single correct method. The right one depends on how you pay people and what your written policy says. Below are the five common methods, the math for each, and a formula you can paste into Google Sheets or Excel.

Formulas use commas between arguments (US English default). If your spreadsheet locale uses semicolons, swap them. Every formula on this page was checked in a test spreadsheet; the example numbers below are the results it produced.

The example we'll use

A full-time employee gets 15 days (or 80 hours) a year, was hired on June 1, 2026, and we're calculating their balance as of October 17, 2026. Their pay period is every two weeks, with the first payday of the year on January 9. Put these in cells like this:

B1 year start (2026-01-01), B2 as-of date, B3 year end, B4 first payday
On the employee row (row 6): B6 hire date, C6 annual days, D6 annual hours, E6 hours worked so far, F6 carryover from last year, G6 carryover cap, H6 days already taken

Year end, so you never hard-code it:

=EDATE(B1,12)-1

Method 1: Per hour worked

Common for hourly and part-time staff. The rate is the annual hours divided by the hours in a full-time work year. 40 hours × 52 weeks = 2,080, so 80 hours a year works out to about 0.0385 hours of PTO per hour worked.

=D6/2080

=ROUND(E6*D6/2080,2)

With 1,200 hours worked, the employee has accrued 46.15 hours.

Method 2: Per pay period

Each paycheck adds an equal slice: annual days ÷ 26 for biweekly pay (24 for twice-monthly, 52 for weekly, 12 for monthly). This formula counts how many biweekly paydays have happened by the as-of date:

=IF($B$2<$B$4,0,INT(($B$2-$B$4)/14)+1)

That's 21 paydays from Jan 9 to Oct 17. Accrued for someone employed all year:

=ROUND(C6/26*IF($B$2<$B$4,0,INT(($B$2-$B$4)/14)+1),2)

Result: 15 ÷ 26 × 21 = 12.12 days. For a mid-year hire, count paydays from their first payday instead of B4.

Method 3: Monthly (completed months)

Annual days ÷ 12 for each full month worked this year. DATEDIF counts completed months; adding 1 to the as-of date makes the month that ends today count.

=ROUND(C6/12*DATEDIF(MAX(B6,$B$1),$B$2+1,"m"),2)

June 1 to October 17 is 4 completed months: 15 ÷ 12 × 4 = 5.00 days.

Method 4: Daily proration

The smoothest method: the allowance is spread across every day of the year, so balances grow a little each day and mid-year hires are prorated automatically.

=ROUND(C6*MAX(0,MIN($B$2,$B$3)-MAX(B6,$B$1)+1)/($B$3-$B$1+1),2)

June 1 to October 17 is 139 days of a 365-day year: 15 × 139 ÷ 365 = 5.71 days. MAX(0, ...) keeps the balance at zero for people who haven't started yet.

Method 5: Front-loaded (lump sum)

Not accrual at all. The full allowance is granted on the first day of the year (or prorated once for a new hire). It's the simplest to track: the accrued figure is just C6, or Method 4's formula with the as-of date replaced by the year end.

Carryover caps and balance caps

Most policies limit how much unused time rolls into the new year. Cap the carryover:

=MIN(F6,G6)

Then the available balance is capped carryover + accrued − taken (shown with daily accrual):

=MIN(F6,G6)+ROUND(C6*MAX(0,MIN($B$2,$B$3)-MAX(B6,$B$1)+1)/($B$3-$B$1+1),2)-H6

With 8 days carried over (capped at 5) and 4 days taken: 5 + 5.71 − 4 = 6.71 days. If your policy also caps the total balance (say 20 days), wrap the whole thing: =MIN(20, ...).

Want the numbers without the formulas? Try the free PTO accrual calculator.

Which method should you use?

Hourly or variable-schedule staff: per hour worked.
Payroll-driven teams: per pay period, so the balance matches the pay stub.
Simple salaried teams: monthly or daily proration.
Smallest admin effort: front-loaded.

Whatever you pick, write it down in your policy and apply it to everyone the same way. Our PTO policy template has fill-in wording for each method.

Skip the setup

Simple PTO Tracker is a ready-made Google Sheets / Excel tracker that uses daily proration with carryover caps, plus a request log, balances and a monthly calendar. The free Lite version handles up to 5 employees. The full version ($14) handles up to 100.

Not legal advice. Leave rules vary by state and sometimes by city (for example required paid sick leave, limits on "use it or lose it", and payout of unused vacation when someone leaves). Check the rules that apply to you before setting a policy.