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 each person has left. Below are six trackers that answer it, and the decisions that keep the answer right. I run a small company and build HR software for small businesses, and in a PTO file I care less about clever formulas than about a number everyone trusts.
The question sounds simple until you answer 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 of those files fail for the same reason: they were built before anyone decided what the rules were.
The six trackers are 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.
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.
The spreadsheet is a record, not 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 questions 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 time-off requests start to overlap.
Using these as a vacation tracker
If you offer vacation on its own rather than a combined bank, the same files work as a vacation tracker with nothing changed. Keep the V code for vacation days, ignore the codes you do not use, and the balance columns simply read as vacation accrued, used, and left.
The per-employee balance tracker is the one people mean by an employee vacation tracker: one row per person with accrued, used, scheduled, and available side by side, and a transaction log behind it so any figure traces back to a date and an approver.
Turning the annual sheet into a shared time off calendar
The annual calendar tracker is the time off calendar most small teams need: people down the side, months across the top, days taken in each cell, and a legend saying which leave codes draw down the balance and which do not. One workbook gives you the shape of the whole year.
For the week-level view an absence calendar usually implies, use the coverage planner instead. It lists who is out each week against a minimum staffing threshold, marks blackout periods, and shows a pending request beside the coverage conflict it would create, before you approve it.
Tracking a leave of absence
A leave of absence needs its own row, not a line in the PTO bank. Protected family or medical leave, extended unpaid leave, and jury duty each run on their own rules, so a leave of absence tracker holds start and end dates rather than a drawn-down balance.
The legend carries codes for protected leave, unpaid leave, jury duty, and bereavement, all set not to count against PTO. Record the start and end dates in the log sheet, keep eligibility and paperwork with your leave of absence policy, and check the rule in every state where you employ people.
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 | 9 | 4 | 6.5 | 2.5 |
| 3 | Sample: J. Okafor | 2024-07-01 | 15 | 0 | 9.4 | 5 | 2 | 4.4 | 2.4 |
| 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 setup steps to work through before the tracker goes live, 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; the calculator workbook has the full set.
| 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, work out their full-time equivalent (FTE) and 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.
What the Spreadsheet Cannot Decide
Four things sit outside the file and decide whether its numbers are right. Each is a place where small businesses commonly get caught.
Because state rules vary so much, the habit that holds up for a small business is to keep the tracker accurate and consistent with a written policy. Together, those two things settle most disputes without needing a legal opinion.
Days vs Hours
Track in hours if you have any hourly or part-time staff; days are fine only for an all-salaried team on a single fixed schedule. Choosing a tracking unit sounds like a formatting preference, but it is a structural decision, and 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 but reconciliation time and trust, and as a rule of thumb it 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
These workbooks work on their own, but a download is only the starting point. 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.
Where FirstHR fits is the written policy every balance depends on. The time off policy goes out with built-in e-signature, each signed acknowledgment is stored in the employee's file, and employees get self-service visibility into their own profile and signed documents. The balances themselves stay in the trackers above.
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?
Settle the rules first and build the file second. Before typing a single number, pick one unit, days or hours; decide whether people receive their time up front as a lump sum or earn it across pay periods; fix the accrual rate; choose a plan year that follows either the calendar or each employee's hire date; and set any carryover cap. The workbook then needs three parts: a balances sheet with a row per employee for entitlement, carryover, accrued, used, and available time; a dated log in which every accrual and every absence is entered with the name of whoever approved it; and a holiday list, so company holidays never come out of anyone's PTO. Post accruals on one fixed day each period, and enter absences as soon as they are approved instead of catching up once a month. These files rarely break because of a bad formula. They break because nobody kept them current, so a steady update schedule matters more than a clever workbook. This is general information, not legal advice.
How is PTO accrual calculated?
Take the yearly entitlement and divide it by how many accrual periods fall in a year. A grant of 15 days on an 8-hour standard day is 120 hours a year. Spread over 26 biweekly pay periods, that comes to roughly 4.6154 hours each time; over 24 semimonthly periods it is exactly 5 hours, and over 12 monthly periods exactly 10 hours. Accruing per hour worked uses the full-time hours in a year as the divisor instead: 120 divided by 2,080 gives about 0.0577 hours of PTO for every hour someone works. That is the fairest option when weekly hours swing, because the accrual rises and falls with the hours. Part-timers should get a prorated grant rather than the full one, scaled by their scheduled hours, so a person working 24 hours a week earns 0.6 of the full-time entitlement. There is no need to work these out by hand: the accrual calculator on this page lists them, already computed, for the common entitlements. This is general information, not legal advice.
Should I track PTO in days or hours?
Hours, if any of your employees are hourly or part-time; days only work when everyone is salaried on one fixed schedule. A day off is not a fixed amount of time for someone whose week is uneven. If a person works a six-hour Tuesday and a ten-hour Thursday, the day they take off decides what it really costs them, so a file kept in days quietly charges them too much or too little. Hours also deal with partial absences cleanly, while days push you into fractions that each person writes down a little differently. Above all, stay consistent: settle on one unit, label it at the head of the file, and never mix the two. A sheet that holds some balances in days and others in hours, with a half day logged as 0.5 in one place and as 4 in another, is the most common way these trackers lose people's trust. If employees find days easier to read, convert to days for display only. This is general information, not legal advice.
Is there a federal law requiring paid time off?
No. Federal law does not require private employers to offer paid vacation, paid holidays, or any general paid time off, and it does not require a payout of unused time when someone leaves; the Department of Labor treats these benefits as a matter of agreement between employer and employee. With no national floor, states and cities have stepped in, each in its own way, which is why the answers vary so widely. A number of states and localities now require paid sick leave and set their own rules for how it accrues, what it may be used for, and how much carries over. Separately, several states count accrued vacation as earned wages, so it cannot be forfeited, use-it-or-lose-it policies are barred or limited, and the unused balance must be paid out at separation. Elsewhere, the employer's written policy generally decides what happens to unused time. In practice, look up the rule in each state where you have employees, and keep balances accurate, because in some states they are 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. Federal law requires no payout at all. In several states, including California and Colorado, accrued vacation counts as earned wages: it cannot be forfeited and has to be paid in the final paycheck, whatever your policy says. In most other states the policy governs. If it promises a payout, you owe one; if it clearly says unused time is forfeited when employment ends, that generally stands. When a payout is due, it is normally the accrued balance multiplied by the employee's current rate of pay, so a mistake in the tracker turns straight into a mistake in money. Sick leave is sometimes handled differently from vacation, but folding everything into one PTO bank can make sick time payable at separation when it otherwise would not be. Confirm the rule for every state you employ people in. This is general information, not legal advice.
Should sick leave be tracked separately from PTO?
If you operate anywhere that mandates paid sick leave, yes: give sick time its own bucket, even if employees see one combined total. Mandates like these usually set their own accrual rate, spell out the reasons the time may be used, impose carryover requirements, and limit when you can require documentation for a short absence. Pool everything into one balance and it becomes hard to prove that the required time was provided on the required terms; a combined bank can also create a payout obligation for sick time that would not otherwise exist. Where no mandate applies, a single combined bank is easier to run, and many employees prefer it because it takes away any reason to call in sick just to use their time. The templates on this page tag each absence with a leave-type code, so the category is on record even when you present one balance. This is general information, not legal advice.
How many employees can a PTO spreadsheet handle?
Technically hundreds, since a spreadsheet holds that many rows without trouble; in practice, a reasonable rule of thumb puts the limit at about 20 to 25 people. What gives out first is not the number of rows but the time it takes to keep the file reconciled and the trust people place in its numbers. Watch for the signs. Staff keep coming to you for their balance, so every answer means opening a file that could be a week out of date. A second copy of the workbook turns up. Requests reach you by text, by email, and in passing instead of through one channel. A departing employee's payout has to be pieced together from memory. Someone spots a mistake in their own balance, and confidence in every other number drops with it. None of this is a software problem. Each sign comes from a manual process that only works while one person remembers to update it. You will find the same signals, listed plainly, in the setup checklist. This is general information, not legal advice.
What is the difference between a PTO tracker and a PTO policy?
The policy sets the rules and the tracker applies them. Your policy is where you decide who qualifies, how much time each person gets, whether it is granted up front or earned over time and at what rate and frequency, when the plan year starts over, how much can roll into the next year, how long new hires wait, how people request time and who approves it, and what happens to unused time when someone leaves. The tracker is the ledger that runs those rules against real names and dates. The classic mistake is building the tracker first: the file then meets questions the policy never answered, and someone makes up a rule on the spot under time pressure. Write the policy before anything else, even if it fits on one page, and then set up the tracker to mirror it. If a balance is challenged later, you are checking math against a rule already on paper rather than debating what the rule was meant to be. This is general information, not legal advice.