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
If you are writing this yourself: model the ledger first, keep the three balance types distinct, prorate on days rather than months, and version your jurisdiction rules by effective date. If you would rather not write it, this is what I build at PTODesk — happy to answer questions about any of the above in the comments.
Top comments (0)