PTO Accrual Formulas for Excel (Copy-Paste, With Caps)

Accrual formulas are short. Getting them right is mostly about three things: counting periods correctly, not rounding too early, and enforcing a cap in a way that still works after someone takes time off.

Everything here works identically in Excel and Google Sheets. Cell references assume a layout given under each formula.

The building blocks

Set these up once, on a Settings tab, and reference them everywhere. Hard-coding the allowance into thirty formulas is how a spreadsheet becomes unmaintainable.

Rate per pay period:

=$B$1/$B$2

Do not round this. 120 ÷ 26 = 4.6153846..., and that trailing precision is the difference between hitting 120 hours a year and drifting.

Rate per hour worked (for part-timers and hourly staff):

=$B$1/($B$4*52)

For 120 hours and a 40-hour week: 0.0577 hours of PTO per hour worked.

Formula 1: periods elapsed

You need to know how many pay periods have run. The robust way is a pay calendar — a column of pay dates on a Settings tab — and a COUNTIF:

=COUNTIF(PayDates,"<="&TODAY())

Where PayDates is your list of pay period end dates. This handles the 27-pay-period year, mid-year changes, and everything else, because it counts what actually happened rather than estimating.

The quick approximation, if you have no pay calendar:

=DATEDIF($B$5,TODAY(),"m")

B5 is the start of the accrual year. This gives completed whole months. For monthly accrual that is correct; for biweekly it is an estimate that will be off by up to a period.

Formula 2: accrued to date

Gross accrual since the start of the year, ignoring anything taken:

=periods_elapsed * $B$1/$B$2

For a mid-year hire, replace periods_elapsed with periods since their start date:

=COUNTIFS(PayDates,"<="&TODAY(),PayDates,">="&hire_date) * $B$1/$B$2

This is why accrual prorates itself — a March hire simply has fewer periods counted, with no proration table anywhere.

Formula 3: current balance

Accrued, plus carryover, minus what has been taken:

=accrued_to_date + carryover - taken

Where taken comes from a SUMIFS over your request log:

=SUMIFS(Log[Hours],Log[Employee],$A2,Log[Status],"Approved")

Add a second SUMIFS for pending requests, and a genuinely available figure:

=balance - SUMIFS(Log[Hours],Log[Employee],$A2,Log[Status],"Pending")

The difference between "balance" and "available" is what stops you approving two requests that each fit and together do not.

Formula 4: the accrual cap

Here is where most spreadsheets get it subtly wrong.

The naive version caps the gross accrual:

=MIN($B$3, accrued_to_date) + carryover - taken

This looks right and is wrong. A true accrual cap caps the balance and resumes accruing once the employee takes time off. The naive formula permanently stops counting accrual past the cap, so an employee who hits 180 hours in October and then takes two weeks never earns those hours back.

The correct behavior needs the running balance capped period by period, which a single cell formula cannot express — it is genuinely iterative. Two practical options:

Option A — a period-by-period table. One row per pay period per employee, each row computing:

=MIN($B$3, previous_balance + rate - taken_this_period)

Correct, and it produces an audit trail. Also 26 rows per person per year, which for ten people is 260 rows to maintain.

Option B — accept the approximation. Use the naive MIN and manually review anyone sitting at the cap. Fine for a small team where hitting the cap is rare, misleading if it is common.

Formula 5: prorated first-year allowance

If you front-load rather than accrue, a mid-year hire needs a partial grant:

=ROUND($B$1 * (12-MONTH(hire_date)+1)/12, 1)

Someone hired in April gets 9/12 of the allowance. Round to one decimal or to the nearest half day, depending on how you handle partial days. The prorated PTO calculator does the same thing if you would rather not maintain the formula.

Formula 6: working days in a request

Days off should exclude weekends and company holidays:

=NETWORKDAYS(start_date, end_date, Holidays)

For a non-Monday-to-Friday week, NETWORKDAYS.INTL takes a weekend argument:

=NETWORKDAYS.INTL(start_date, end_date, 7, Holidays)

Where 7 means Friday and Saturday are the weekend. The full list of weekend codes is in Excel's function help; 1 is the default Saturday/Sunday.

The precision trap, demonstrated

This is the single most common cause of year-end discrepancies, so it is worth seeing.

Twelve minutes a year sounds trivial. Across ten employees over five years it is a day and a half of PTO nobody can account for, and it surfaces as an employee insisting their balance is wrong — and being right.

Rule: round the display, never the rate. Format cells to two decimals rather than wrapping the formula in ROUND.

A minimal working layout

Four tabs, and you have a functioning accrual tracker:

How to build a PTO tracker in Google Sheets walks through this structure step by step, and the ready-made template has it built already.

Where formulas stop being the answer

Everything above is achievable and, for a fixed annual allowance, genuinely fine. The point where it stops being worth it is fairly precise:

• Caps that people actually hit, because correct behavior requires a period-by-period state machine.
• Multiple accrual rates, once tenure ladders or part-time rates multiply the formulas.
• Anyone needing notification, because no formula sends an email.
• More than one editor, because SUMIFS silently returns zero for a mistyped name and a wrong balance looks exactly like a right one.

SimplyPTO runs these rules as rules rather than formulas: accrual at full precision, caps that freeze and resume correctly, part-time proration by hours worked, and balances that cannot silently disagree with the request log. If you have a spreadsheet already, it imports directly. Free for up to 10 people.