PTO accrual is date math, not a spreadsheet column: four things that break

PTODesk — Tue, 22 Sep 2026 21:45:11 +0000

Most small teams track paid time off in a shared spreadsheet. It works right up until it doesn't, and the failures are always the same four. I have spent the last while building a leave tracker, so here is what I learned about the math.

1. A balance should be a sum, not a cell

If the balance lives in a cell, you can never answer "why do I have 12.5 days?". Store every event instead — opening balance, each accrual, each booking, each adjustment — and derive the balance as a sum of the ledger. It costs you a join and buys you an audit trail plus the ability to show an employee the arithmetic instead of asking them to trust it.

opening   +10.96
accrual   + 0.63   (pay period 17)
usage     - 1.00
--------------------
available   10.59

2. Accrued, available and projected are three different numbers

Employees read one number and assume it is theirs. It usually isn't:

accrued — what the ledger says has been earned to date
available — accrued minus days already booked but not yet taken
projected — accrued plus what will accrue by a future date

A request in June for two weeks at Christmas is valid against projected, not against available. Systems that only model available force managers back into a side calendar, which defeats the point of tracking at all.

3. Mid-year proration is a real formula, and it is easy to get wrong

Hire someone on 15 June on a 20-day policy and they do not get 20 days, and they do not get 10 either. Over a calendar year:

20 × 200 / 365 = 10.96 days

200 being the days remaining in the year from the hire date. Rounding this to "half the year, so half the entitlement" is the single most common spreadsheet bug I have seen, and it compounds because next year's carryover is computed from it.

4. US state sick leave does not use one denominator

If you are building for US teams: seventeen states and DC mandate paid sick leave accrual, and they do not agree on the rate.

Rate	Where
1 hr / 30 worked	14 states
1 hr / 35 worked	Rhode Island
1 hr / 37 worked	DC (varies by employer size)
1 hr / 40 worked	Washington
1 hr / 52 worked	Vermont

Caps differ too — Minnesota and Washington have no annual cap at all. The practical lesson is that the rule cannot be a constant in your code. It needs an effective date and a citation, so that when a legislature changes something you can see what changed, when, and which balances were computed under the old rule.

Takeaway