PTO Tracking Spreadsheet for Small Business
Free PTO tracking spreadsheets: annual calendar, per-employee balances, accrual calculator, team coverage, and a simple version for teams under 10. XLSX.
PTO Tracking Spreadsheet
Six paid time off trackers for US small business: an annual calendar view, a per-employee balance tracker with a transaction log, an accrual calculator covering every common method, a team coverage planner, a simple version for teams under 10, and a setup and audit checklist. Every accrual rate is precomputed, and part-time proration is built in. Download as XLSX, no signup.
A PTO tracking spreadsheet answers one question reliably: how much time off does each person have left. That sounds simple until you are running it for a dozen people across accruals, carryover, half days, holidays that should not count, and a new hire who started in March. Most small businesses start with a spreadsheet because it is free and immediate, and most spreadsheets fail for the same reason, which is that they were built before anyone decided what the rules actually were.
There are six trackers here: an annual calendar view, a per-employee balance tracker with a running transaction log, an accrual calculator with every common rate precomputed, a team coverage planner, a deliberately simple version for teams under ten, and a setup and audit checklist. Each downloads as an editable Excel workbook, free and without an email. They pair with a written time off policy, which is what the tracker is supposed to be recording.
What a PTO Tracking Spreadsheet Does
A PTO tracking spreadsheet is a ledger of paid time off: how much each employee is entitled to, how much they have earned so far, how much they have used, and what remains. At minimum it holds a balance row per person, a log of absences with dates and approvals, and a list of company holidays that should not draw down anyone's balance.
What it is not is a decision-making tool. It cannot tell you whether time accrues or arrives as a lump sum, or whether unused days carry over, because those are policy questions. When a spreadsheet is built before those are settled, it ends up encoding whatever the person building it assumed, which is how balances become arguable.
Decide These Before You Build
Every question below has to be answered before the first number goes in the file, because each one changes what the columns mean. Answering them afterward means rebuilding.
Write the answers down, ideally in a one-page policy, and then build the tracker to match. This ordering is the difference between a file that settles disputes and a file that causes them.
Which Template Should You Use?
Team size decides most of it. Under ten people, the simple tracker is genuinely enough. Above that, the balance tracker plus the annual calendar covers the common case, and the coverage planner earns its place once overlapping requests start colliding.
6 Free PTO Trackers
Download all six as one bundle or take individual workbooks. Each opens in Excel, Google Sheets, or Numbers. Sample rows are included so the structure is obvious; delete them and enter your own.
Template 1: Annual PTO Calendar Tracker
A month-by-month view split across two half-year sheets, with a leave-type legend defining which codes draw down the balance and which do not, plus a company holidays tab so paid holidays never get counted as PTO by accident.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Leave Type | Counts Against PTO Balance | Notes |
| 2 | V | Vacation | Yes | Planned time off, approved in advance |
| 3 | S | Sick | Yes, if you run a combined PTO bank | Track separately if your state mandates paid sick leave |
| 4 | P | Personal | Yes | Personal days under your policy |
| 5 | H | Company Holiday | No | Paid holiday, does not draw down the balance |
| 6 | U | Unpaid | No | Approved unpaid absence |
| 7 | B | Bereavement | No | Separate entitlement under your policy |
| 8 | J | Jury Duty | No | Separate entitlement, often state-mandated |
| 9 | L | Protected Leave | No | Family or medical leave, tracked separately |
| 10 | HD | Half Day | Counts as 0.5 | Append to any code, for example V-HD |
Template 2: Per-Employee Balance Tracker
Entitlement, carryover, accrued, used, and scheduled per person, with an available-after-scheduled column that catches over-booking before it happens. The transaction log posts a running balance for every accrual and absence.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Hire Date | Annual Entitlement (days) | Carryover In | Accrued YTD | Used YTD | Scheduled Future | Available Now | Available After Scheduled |
| 2 | Sample: A. Rivera | 2022-03-14 | 20 | 3 | 12.5 | 21 | 4 | -5.5 | -9.5 |
| 3 | Sample: J. Okafor | 2024-07-01 | 15 | 0 | 9.4 | 17 | 2 | -7.6 | -9.6 |
| 4 | Sample: M. Chen | 2019-11-20 | 25 | 5 | 15.6 | 14.5 | 6 | 6.1 | 0.1 |
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 |
Template 3: PTO Accrual Calculator
Accrual rates precomputed for every common entitlement across weekly, biweekly, semimonthly, monthly, and per-hour-worked methods, plus a settings tab, tenure tiers, and a part-time proration table.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Annual PTO (days) | Annual PTO (hours) | Per Weekly Period | Per Biweekly (26) | Per Semimonthly (24) | Per Monthly (12) | Per Hour Worked (2080) |
| 2 | 10 | 80 | 1.5385 | 3.0769 | 3.3333 | 6.6667 | 0.038462 |
| 3 | 12 | 96 | 1.8462 | 3.6923 | 4 | 8 | 0.046154 |
| 4 | 15 | 120 | 2.3077 | 4.6154 | 5 | 10 | 0.057692 |
| 5 | 18 | 144 | 2.7692 | 5.5385 | 6 | 12 | 0.069231 |
| 6 | 20 | 160 | 3.0769 | 6.1538 | 6.6667 | 13.3333 | 0.076923 |
| 7 | 22 | 176 | 3.3846 | 6.7692 | 7.3333 | 14.6667 | 0.084615 |
| 8 | 25 | 200 | 3.8462 | 7.6923 | 8.3333 | 16.6667 | 0.096154 |
| 9 | 30 | 240 | 4.6154 | 9.2308 | 10 | 20 | 0.115385 |
Template 4: Team Overview and Coverage Planner
Week-by-week coverage against a minimum staffing threshold, blackout periods for peak season or quarter close, and a pending-requests queue showing the resulting balance and any coverage conflict before you approve.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Week Starting | Team Size | Out on PTO | Coverage Percent | Minimum Required | Status | Who Is Out |
| 2 | 2026-06-01 | 8 | 1 | 88% | 75% | OK | A. Rivera |
| 3 | 2026-06-08 | 8 | 2 | 75% | 75% | At limit | A. Rivera, J. Okafor |
| 4 | 2026-06-15 | 8 | 3 | 63% | 75% | Over limit | A. Rivera, J. Okafor, M. Chen |
| 5 | 2026-06-22 | 8 | 1 | 88% | 75% | OK | M. Chen |
| 6 | 2026-06-29 | 8 | 0 | 100% | 75% | OK | |
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 | |||||||
| 11 |
Template 5: Simple Tracker for Teams Under 10
Two sheets, no formulas to maintain, deliberately minimal. Granted, carryover, taken, booked, and left, with a plain time-off log alongside. For a team where a full workbook is more overhead than help.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Days Granted | Carryover | Total Available | Days Taken | Days Booked | Days Left | Last Updated |
| 2 | Sample: A. Rivera | 15 | 3 | 18 | 11 | 4 | 3 | 2026-06-30 |
| 3 | Sample: J. Okafor | 15 | 0 | 15 | 7 | 5 | 3 | 2026-06-30 |
| 4 | Sample: M. Chen | 20 | 2 | 22 | 12 | 0 | 10 | 2026-06-30 |
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 | ||||||||
| 11 | Team total |
Template 6: Setup and Audit Checklist
The ten decisions to make before entering a single number, a monthly audit routine that catches the errors these files actually develop, and an honest list of the signals that a spreadsheet has stopped being the right tool.
| A | B | C | |
|---|---|---|---|
| 1 | Step | Task | Why It Matters |
| 2 | 1 | Decide days or hours as your tracking unit | Hourly staff and part-timers are far easier to track in hours; mixing units is the most common source of errors |
| 3 | 2 | Decide combined PTO bank or separate buckets | Several states mandate paid sick leave with their own rules, which is hard to prove from a combined bank |
| 4 | 3 | Set the accrual method and rate | Lump sum, weekly, biweekly, semimonthly, monthly, or per hour worked; write the rate down |
| 5 | 4 | Set the plan year | Calendar year, hire-date anniversary, or fiscal year; this decides when balances reset |
| 6 | 5 | Set carryover and any caps | Check your state first, since several prohibit forfeiting accrued vacation |
| 7 | 6 | Set the waiting period for new hires | How long before newly accrued time can be used |
| 8 | 7 | List company holidays | Holidays should not draw down the PTO balance |
| 9 | 8 | Enter opening balances for every employee | Get this right once; every later number depends on it |
| 10 | 9 | Decide who approves and who updates the file | One owner, one file; two copies means two truths |
| 11 | 10 | Write the matching policy and share it | The tracker records what the policy decides, not the other way around |
The Accrual Math
Accrual is a division problem: the annual entitlement divided by the number of periods in a year. The table below shows the per-period figures for common entitlements at an 8-hour standard day, which is what the calculator workbook contains in full.
| Annual days | Annual hours | Biweekly (26) | Semimonthly (24) | Monthly (12) | Per hour worked |
|---|---|---|---|---|---|
| 10 days | 80 hours | 3.0769 hrs | 3.3333 hrs | 6.6667 hrs | 0.038462 hrs |
| 15 days | 120 hours | 4.6154 hrs | 5.0000 hrs | 10.0000 hrs | 0.057692 hrs |
| 20 days | 160 hours | 6.1538 hrs | 6.6667 hrs | 13.3333 hrs | 0.076923 hrs |
| 25 days | 200 hours | 7.6923 hrs | 8.3333 hrs | 16.6667 hrs | 0.096154 hrs |
Per-hour-worked accrual divides the annual hours by 2,080, the standard full-time year. It is the fairest method for staff whose hours vary, because someone who works fewer hours accrues proportionally less without any manual proration. For part-time employees on a fixed reduced schedule, prorate the annual entitlement by the FTE fraction instead: 24 hours a week is 0.6 of full time, so a 15-day entitlement becomes 9 days. For more background, see how accrued PTO works.
What the Spreadsheet Cannot Decide
Four things sit outside the file and determine whether what is in it is correct. Each is a place where small businesses commonly get caught.
Because the state layer varies so much, the durable habit for a small business is to keep the tracker accurate and keep it consistent with a written policy. Those two things together answer most disputes without needing a legal opinion. For the payout question specifically, see whether companies have to pay out PTO, and for the state-by-state picture, PTO laws by state.
Days vs Hours
Choosing a tracking unit sounds like a formatting preference and is actually a structural decision. The table below is the short version.
| Track in days | Track in hours | |
|---|---|---|
| Best for | All-salaried teams on one fixed schedule | Any hourly or part-time staff |
| Partial absences | Awkward fractions | Natural |
| Variable schedules | Inaccurate, a day is not a fixed amount | Accurate |
| Part-time proration | Manual and error-prone | Automatic with hourly accrual |
| Readability for employees | Intuitive | Convert for display |
| Payout calculation | Requires conversion | Direct |
If you are unsure, pick hours. Converting hours to days for display is trivial; converting inconsistently recorded days back into hours after eighteen months is not. Write the chosen unit at the top of the file so nobody has to guess.
When a Spreadsheet Stops Working
A spreadsheet is the right tool for longer than software vendors suggest and shorter than most owners hope. The limit is not the row count, it is reconciliation time and trust, and it usually arrives somewhere around twenty to twenty-five people.
The signals are consistent. Employees start asking you what their balance is, which means every question is a manual lookup against a file that may be a week stale. Two versions of the file appear. Requests arrive by text, email, and conversation instead of through one queue. A payout at separation has to be reconstructed. And someone finds an error in their own balance, which quietly costs you the trust that made tracking worthwhile in the first place.
Set Up, Log, and Audit
A downloaded workbook is the starting point, and these work on their own. The strain shows up in the upkeep: accruals posted late, requests approved verbally and never logged, a balance nobody has reconciled since March, and no dated backup when a bad edit propagates.
To run time off without the manual reconciliation, FirstHR keeps balances updated automatically as requests are approved, gives employees self-service visibility into their own balance so the lookups stop landing on you, routes every request through one approval queue, and keeps the record against the employee profile rather than in a file someone has to remember to open. FirstHR is an onboarding and HR platform, not a payroll provider or a law firm: it does not run payroll, calculate a separation payout, or decide what your policy should say, so pair it with your payroll provider and a qualified attorney for those calls. Applicant tracking is coming soon to FirstHR.
Frequently Asked Questions
How do I track PTO in a spreadsheet?
Start with the decisions, not the file. Choose days or hours as your tracking unit, decide whether time is granted as a lump sum or accrued over pay periods, set the accrual rate, choose whether the plan year runs on the calendar or on each employee's hire date, and set any carryover cap. Then build three things: a balance sheet with one row per employee showing entitlement, carryover, accrued, used, and available; a log where every absence and every accrual is recorded with a date and an approver; and a list of company holidays that should not draw down anyone's balance. Update accruals on the same day each period and log absences as they are approved rather than in a monthly catch-up. The most common failure is not a formula error but a stale file, so the discipline of updating on schedule matters more than the sophistication of the workbook. This is general information, not legal advice.
How is PTO accrual calculated?
Divide the annual entitlement by the number of accrual periods in a year. If you grant 15 days at an 8-hour standard day, that is 120 hours annually. Across 26 biweekly pay periods that is roughly 4.6154 hours per period; across 24 semimonthly periods it is exactly 5 hours; across 12 monthly periods it is exactly 10 hours. For hourly accrual, divide the annual hours by full-time annual hours, so 120 divided by 2,080 gives about 0.0577 hours of PTO per hour worked, which is the fairest method for staff whose hours vary week to week. Part-time entitlement should be prorated by scheduled hours rather than granted in full, so someone working 24 hours a week receives 0.6 of the full-time entitlement. The accrual calculator on this page has every one of these rates precomputed for common entitlements so you can look yours up rather than deriving it. This is general information, not legal advice.
Should I track PTO in days or hours?
Hours, if you have any hourly or part-time employees; days are acceptable only when everyone is salaried on the same fixed schedule. The reason is that a day is not a consistent quantity for someone whose schedule varies. If an employee works six hours on Tuesday and ten on Thursday, taking a day off means two different amounts depending on which day it is, and tracking in days quietly under-charges or over-charges them. Hours also handle partial absences cleanly, whereas days force you into fractions that different people record differently. The rule that matters most is consistency: pick one unit, write it at the top of the file, and never mix them. A spreadsheet with some rows in days and some in hours, and half days entered as 0.5 in one column and 4 in another, is the most common way these files become untrustworthy. Convert to days for display if that reads better to employees. This is general information, not legal advice.
Is there a federal law requiring paid time off?
No. There is no federal law requiring private employers to provide paid vacation, paid holidays, or general paid time off, and none requiring that unused time be paid out when employment ends. That is precisely why the rules vary so much: with no federal floor, states and cities have filled the gap in different ways. A number of states and localities now mandate paid sick leave with their own accrual rates, permitted uses, and carryover rules. Separately, several states treat accrued vacation as earned wages that cannot be forfeited, which prohibits or limits use-it-or-lose-it policies and requires payout of the balance at separation. In the remaining states the employer's written policy generally controls what happens to unused time. Two practical implications: check the rule in every state where you have employees, and keep balances accurate, because in some states they represent money you may owe. This is general information, not legal advice.
Do I have to pay out unused PTO when an employee leaves?
It depends on your state and on your written policy. No federal law requires a payout. In several states accrued vacation is treated as earned wages, which means it cannot be forfeited and must be included in the final paycheck regardless of what your policy says. In most other states the employer's policy controls, so if your policy promises a payout you owe it, and if your policy clearly states that unused time is forfeited on separation, that generally holds. A few practical points. Where a payout is owed, it is typically calculated at the employee's current rate of pay against the accrued balance, which is why an accurate tracker has direct financial consequences. Sick leave is sometimes treated differently from vacation, but combining everything into one PTO bank can pull sick time into a payout obligation it would not otherwise carry. Check the rule for each state where you employ people. This is general information, not legal advice.
Should sick leave be tracked separately from PTO?
If you operate anywhere with a paid sick leave mandate, yes, track it as its own bucket even if you show employees a combined total. Those laws typically prescribe their own accrual rate, the reasons the time may be used, carryover requirements, and limits on requiring documentation for short absences. If everything sits in one undifferentiated bank, proving you provided the mandated time on the mandated terms becomes difficult, and a combined bank can also drag sick time into a payout obligation it would not otherwise have. Where no mandate applies, a single combined bank is simpler to administer and many employees prefer it because it removes the incentive to claim illness in order to use time. The templates on this page carry separate leave-type codes so you can record the category on each absence while still presenting a combined balance if you choose. This is general information, not legal advice.
How many employees can a PTO spreadsheet handle?
Mechanically, a spreadsheet handles hundreds of rows without complaint. Practically, the limit is not rows but reconciliation time and trust, and for most small businesses that limit arrives somewhere around 20 to 25 people. The warning signs are consistent and worth watching for: employees regularly asking you what their balance is, because every question is a manual lookup against a file that may be a week stale; two versions of the file existing anywhere; requests arriving by text, email, and conversation rather than through one queue; a payout at separation that had to be reconstructed from memory; and somebody finding an error in their own balance, which quietly costs you the trust that made the tracker useful. None of these is about the software. They are about a manual process where the update depends on one person remembering. The setup checklist includes an honest list of these signals. This is general information, not legal advice.
What is the difference between a PTO tracker and a PTO policy?
The policy makes the decisions and the tracker records the consequences. A policy states who is eligible, how much time they receive, whether it is granted up front or accrued, at what rate and frequency, when the plan year resets, how much may carry over, the waiting period for new hires, how requests are submitted and approved, and what happens to unused time at separation. The tracker is the ledger that applies those rules to actual people and dates. Building the tracker first is the classic mistake, because the file then has to answer questions the policy never settled and someone improvises a rule under time pressure. Write the policy first, even a one-page version, then set the tracker up to match it. When a balance is later disputed, the conversation should be about arithmetic against a written rule rather than about what the rule was supposed to be. This is general information, not legal advice.