Quick answer
- For most businesses, the PTO tracker with accrual per pay period is the one to start with: it adds hours each payday, subtracts time used and caps the balance.
- Planning who is off when? The team PTO calendar has 12 month tabs and a daily count of people off.
- All 6 are free Excel files with a printable PDF, no sign-up, and they open in Google Sheets.
A PTO tracker Excel file is the simplest way for a small business to know how much paid time off each employee has earned, used and has left. This page is for US owners, office managers and HR leads who run PTO, vacation or sick leave without dedicated software. There are six templates, each for a different policy: a PTO tracker template that accrues hours every pay period, a paid sick leave tracker that accrues by hours worked, a PTO calendar in Excel for the whole team, a balance summary for all employees, a vacation tracker in days with separate banks, and a sheet that sets PTO by years of service. Each comes with a filled-in example using realistic US sample data, and every total is a working formula.
Which PTO tracker template do you need?
Pick by how your policy gives time off: a little each payday, by hours worked, or a set number of days a year.
| Your policy or task | Best template | Format |
|---|---|---|
| PTO hours earned every payday | 1. PTO tracker with accrual per pay period | Excel, PDF |
| Sick leave earned per hours worked under a state or city law | 2. Paid sick leave tracker | Excel, PDF |
| Seeing who is off on which day | 3. Team PTO calendar | Excel, PDF |
| All balances on one page | 4. Team PTO balance summary | Excel, PDF |
| A set number of vacation, sick and personal days | 5. Vacation tracker | Excel, PDF |
| More PTO the longer people stay | 6. PTO by years of service | Excel, PDF |
1. PTO tracker with accrual per pay period
Best for employers whose PTO builds up a little every payday
PTO tracker · Aisha Johnson, accounts coordinator · Cornerstone Property Management, Richmond, VA · Biweekly · 3.08 hours per pay period (80 a year) · Carryover 16.00 · Cap 120.00 · Balance as of November 7, 2026
| Pay period | Period ends | Accrued | Used | Dates used and notes | Balance |
|---|---|---|---|---|---|
| 1 | 1/9/2026 | 3.08 | 19.08 | ||
| 5 | 3/6/2026 | 3.08 | 31.40 | ||
| 6 | 3/20/2026 | 3.08 | 8.00 | 3/16, dentist and errands | 26.48 |
| 14 | 7/10/2026 | 3.08 | 24.00 | 7/6 to 7/8, family trip | 27.12 |
| 19 | 9/18/2026 | 3.08 | 4.00 | 9/14, afternoon off | 38.52 |
| 22 | 10/30/2026 | 3.08 | 8.00 | 10/26, personal day | 39.76 |
| 23 | 11/13/2026 | 0.00 | Not yet ended |
| Accrued this year (22 periods) | 67.76 hours |
|---|---|
| Used this year | 44.00 hours |
| Balance as of November 7, 2026 | 16.00 + 67.76 − 44.00 = 39.76 hours |
Rows for the other pay periods are hidden here to save space.
This is the PTO tracker Excel template most small businesses need. Type the hours earned per period, the carryover from last year and the cap, then enter hours used in the row for the pay period they fall in. Only periods that have ended accrue, so the balance on the sheet matches what payroll should show today. If the balance reaches the cap, accrual pauses until the employee uses time, which is how a cap works under policies like California’s. Use a PTO request form to collect the approvals that feed the “used” column.
2. Paid sick leave tracker (accrual per hours worked)
Best for hourly teams covered by a state or city paid sick leave law
Paid sick leave tracker · Javier Ortiz, line cook · Harbor Taco Co., San Diego, CA · Earn 1 hour per 30 worked · Cap 80 hours · Annual use limit 40 hours · Carryover 22.00
| Period ends | Hours worked | Sick hours earned | Used | Balance | Used this year |
|---|---|---|---|---|---|
| 1/9/2026 | 72.00 | 2.40 | 24.40 | 0.00 | |
| 1/23/2026 | 80.00 | 2.67 | 27.07 | 0.00 | |
| 2/6/2026 | 64.00 | 2.13 | 8.00 | 21.20 | 8.00 |
| 2/20/2026 | 80.00 | 2.67 | 23.87 | 8.00 | |
| 3/6/2026 | 76.00 | 2.53 | 26.40 | 8.00 | |
| 3/20/2026 | 80.00 | 2.67 | 6.00 | 23.07 | 14.00 |
Earned so far 15.07 · Used 14.00 · Available 23.07 hours · Check column: no warnings (under the 40-hour use limit)
The defaults follow California’s accrual method, which the Labor Commissioner describes as 1 hour of paid sick leave for every 30 hours worked, with a minimum of 40 hours or five days a year, an accrual cap employers may set at 80 hours or 10 days, and use that may be limited to 40 hours or five days a year. Other states and cities set different rates, caps and carryover rules, so change the yellow cells to match the law that covers your workers. California also requires the available balance on the pay stub or a notice issued with it, and sick leave records kept for three years. See our pay stub templates for a stub with a sick leave line.
3. Team PTO calendar
Best for managers planning coverage around holidays and vacations
Team PTO calendar: December · Bluewater Insurance, Charleston, SC · 2026 (type any year) · Weekends and company holidays shaded
| Employee | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | V | S | P | F |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | F | S | S | M | T | W | T | |||||
| Holiday | H | ||||||||||||||||||||||||||||||||||
| Jenna Morris | V | V | V | V | V | V | V | 7 | 0 | 0 | 0 | ||||||||||||||||||||||||
| Luis Ortega | V | V | V | 3 | 0 | 0 | 0 | ||||||||||||||||||||||||||||
| Hannah Lee | S | P | V | 1 | 1 | 1 | 0 | ||||||||||||||||||||||||||||
| Marcus Hill | V | V | V | V | V | 5 | 0 | 0 | 0 | ||||||||||||||||||||||||||
| Olivia Grant | F | F | F | F | F | F | F | F | F | 0 | 0 | 0 | 9 | ||||||||||||||||||||||
| People off | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 2 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 2 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 3 | 2 | 1 | 1 | ||||
Codes: V vacation or PTO · S sick · P personal · F FMLA or other leave · U unpaid. December 28 turns red: three of five people are off. Olivia’s FMLA days are tracked for coverage but kept out of PTO totals.
This PTO calendar in Excel is the tracker to open before you approve a holiday request. Type names once on the summary tab and they appear on every month; type the year and every month redraws. The summary tab adds up each person’s vacation, sick, personal and other days for the year and shows PTO days left. The holiday list starts with the 2026 and 2027 federal holidays as published by the Office of Personnel Management; delete the ones your business stays open for. Our holiday pay guide explains what you owe when people work them.
4. Team PTO balance summary
Best for owners and HR checking every balance before year end
Team PTO balance summary · Brightline Marketing, Minneapolis, MN · Balances as of November 7, 2026 · Hours
| Employee | Annual PTO | Carryover | Earned this year | Used | Approved, not taken | Balance today | Available | Share used | Cap | Flag |
|---|---|---|---|---|---|---|---|---|---|---|
| Kayla Ross | 120 | 16.0 | 101.5 | 88.0 | 8.0 | 29.5 | 21.5 | 65% | 160 | |
| Derek Shaw | 80 | 0.0 | 67.7 | 16.0 | 0.0 | 51.7 | 51.7 | 20% | 120 | Low use, plan time off |
| Maya Patel | 160 | 40.0 | 135.4 | 120.0 | 40.0 | 55.4 | 15.4 | 60% | 240 | |
| Tom Reilly | 120 | 24.0 | 101.5 | 100.0 | 32.0 | 25.5 | −6.5 | 69% | 160 | Over-approved |
| Sofia Lin | 80 | 40.0 | 60.0 | 0.0 | 0.0 | 100.0 | 100.0 | 0% | 100 | At cap, not earning |
| Team totals | 560 | 120.0 | 466.1 | 324.0 | 80.0 | 262.1 | 182.1 |
The summary is the one-page view an owner wants in November. The flags do the reading for you: Tom has been approved for 6.5 more hours than he has, Sofia is at her cap and has stopped earning, and Derek has used 20% of his PTO with weeks left in the year. Approved-but-not-taken time matters because it is already promised; subtract it before you approve anything else. Unused balances are also a liability on your books if your state or policy requires payout. Read our paid time off guide for how to write the policy behind these numbers.
5. Vacation tracker with separate leave banks
Best for salaried and office staff who get a set number of days
Vacation tracker · Ryan Mitchell, project engineer · Keystone Civil Engineering, Pittsburgh, PA · 2026
| Leave bank | Days for the year | Carryover | Used | Remaining |
|---|---|---|---|---|
| Vacation | 15 | 3 | 12.0 | 6.0 |
| Sick | 6 | 0 | 1.5 | 4.5 |
| Personal | 2 | 0 | 1.0 | 1.0 |
| First day | Last day | Type | Workdays (auto) | Days taken | Notes |
|---|---|---|---|---|---|
| Mon 2/16/2026 | Fri 2/20/2026 | Vacation | 4 | 4.0 | 2/16 is a company holiday |
| Thu 4/9/2026 | Sick | 1 | 1.0 | ||
| Mon 6/29/2026 | Fri 7/3/2026 | Vacation | 4 | 4.0 | 7/3 is a company holiday |
| Fri 8/14/2026 | Personal | 1 | 1.0 | ||
| Tue 10/13/2026 | Sick | 1 | 0.5 | Half day, override | |
| Mon 11/23/2026 | Fri 11/27/2026 | Vacation | 4 | 4.0 | Approved; 11/26 is Thanksgiving |
This vacation tracker in Excel is built for policies that grant days rather than hours. Enter the first and last day and the sheet counts Monday-to-Friday workdays, leaving out the company holidays listed on the right, so a week that includes Thanksgiving uses four days, not five. Type 0.5 in the override column for a half day. Ryan has 6 vacation days left for December; if your state or policy lets days expire, the remaining column tells you who needs a reminder now.
6. PTO accrual by years of service
Best for policies that give more PTO as employees stay longer
PTO accrual by years of service · Calculated as of January 1, 2027 · 26 pay periods · Tiers: 0 years 80 hours, 2 years 120, 5 years 160, 10 years 200
| Employee | Hire date | Full years | PTO hours per year | Per pay period | Days per year | Next increase on | New annual hours |
|---|---|---|---|---|---|---|---|
| Grace Kim | 3/15/2025 | 1 | 80 | 3.08 | 10.0 | 3/15/2027 | 120 |
| Omar Haddad | 6/1/2022 | 4 | 120 | 4.62 | 15.0 | 6/1/2027 | 160 |
| Ben Walsh | 1/4/2021 | 5 | 160 | 6.15 | 20.0 | 1/4/2031 | 200 |
| Rachel Torres | 9/10/2016 | 10 | 200 | 7.69 | 25.0 | Top tier | |
| Lena Park | 11/2/2026 | 0 | 80 | 3.08 | 10.0 | 11/2/2028 | 120 |
Tenure tiers are a common way to reward people who stay, and the hard part is remembering when someone moves up. This sheet works it out from the hire date: Grace reaches two years on March 15, 2027, so her accrual should rise from 3.08 to 4.62 hours per pay period that day. Run it at the start of each year and again whenever you hire, then copy each person’s rate into tracker 1. For a wider view of how employers structure PTO, compare notes with free PTO tracker apps.
How to use these PTO tracker templates
- Write down the policy. How PTO is earned (per pay period, per hour worked or a yearly grant), the cap, carryover, how far ahead to request, and what happens at termination. The trackers only apply the rules you give them.
- Set up one file per employee or one summary for all. Use tracker 1, 2 or 5 per person and the balance summary for the team. Keep the PTO calendar for coverage planning.
- Fill in the yellow cells. Accrual rate, carryover, cap and company holidays. Green cells are formulas.
- Update every payday. Enter approved time off and hours worked in the same week payroll runs, so the balance employees see matches their pay stub.
- Check flags monthly. Negative balances, people at the cap and people who have used little PTO by the fall are all easier to fix early.
Each PTO tracker Excel file also opens in Google Sheets or LibreOffice. A spreadsheet holds up for a small team with one manager. Once several managers approve requests, the risk is two people approving the same week from different copies of the file, which is when businesses look at leave management software.
US PTO rules to know
These principles are checked against the Department of Labor and the California Labor Commissioner. PTO law is mostly state and city law, so check your state labor department too.
- Federal law does not require paid vacation. The Department of Labor says the FLSA does not require payment for time not worked, such as vacations, sick leave or federal or other holidays. These benefits are a matter of agreement between employer and employee.
- Paid sick leave is required in many states and cities. California requires at least 40 hours or five days a year, earned at 1 hour per 30 hours worked under the standard accrual method, and lets employers cap accrual at 80 hours or 10 days. Other laws differ, so set tracker 2 to the law that covers each location.
- Earned vacation can be wages. California’s Labor Commissioner says vacation vests as it is earned, use-it-or-lose-it policies are not allowed, a reasonable cap is, and unused vacation must be paid at the final rate of pay when employment ends. Employers can still control when vacation is taken.
- Protected leave is separate. Track FMLA leave as its own type, even on days when paid leave covers it, so it never reduces attendance scores or gets lost in PTO totals. Our FMLA leave guide covers who is eligible.
- Keep the records. Payroll records must be kept for at least three years under the FLSA, FMLA records for no less than three years, and California requires paid sick leave records for three years.
Frequently asked questions
How do I calculate PTO accrual per pay period?
Divide the yearly PTO hours by the number of pay periods. With biweekly payroll there are 26 periods, so 80 hours a year is 3.08 hours per period, 120 hours is 4.62 and 160 hours is 6.15. For accrual by hours worked, divide yearly hours by 2,080: 80 hours a year is about 0.0385 hours per hour worked.
Do employers have to offer paid time off?
Not under federal law. The Department of Labor says the FLSA does not require payment for time not worked, such as vacations, sick leave or holidays. But many states and cities require paid sick leave, and California, for example, requires at least 40 hours or five days a year, so check your state and city.
Can unused PTO expire at the end of the year?
It depends on the state. California’s Labor Commissioner says vacation vests as it is earned, so use-it-or-lose-it policies are not allowed there, although a reasonable cap on accrual is. Many other states allow expiry if the written policy says so, while a few treat earned vacation as wages the way California does. Check your state labor department before you add an expiry date to your tracker.
Do I have to pay out unused PTO when an employee leaves?
Federal law does not require it, but some states do. In California, earned and unused vacation must be paid at the final rate of pay when employment ends, for any reason. Several other states also require payout, and many leave it to your written policy. Keep balances current in your PTO tracker so the final paycheck is right.
Should I track PTO in hours or days?
Hours, if your team works shifts of different lengths or part-time schedules, because payroll pays hours and a day off can be 6 hours for one person and 10 for another. Days work well for salaried office staff with standard 8-hour days, which is why our vacation tracker counts workdays and skips company holidays.
How do I make a PTO calendar in Excel?
Put employees in rows and the days of the month in columns, and type a code such as V for vacation in each day off. Add a row that counts how many people are off each day so you can spot coverage gaps. Our team PTO calendar has 12 month tabs that update for any year and total each person’s days on a summary tab.