Free Excel template
PTO tracker Excel template for 2027
A free PTO tracker for Excel and Google Sheets, built for hours based accrual. Pick weekly, biweekly, semi-monthly or monthly pay, set the accrual rate, the cap and the carryover limit, then log hours as people take time off. The running balance for every pay period and the projected balance at year end work themselves out. It doubles as a vacation tracker. No signup, no email, no macros.
Free .xlsx, 87 KB, 7 sheets, sample rows included. For up to 30 people.
In Google Sheets: File, Import, Upload, then Replace spreadsheet.
- Free, no signup
- No email wall
- No macros
- Excel, Sheets, LibreOffice
What is inside
PTO accrual, cap and year end balance in one workbook
Built the way US payroll works: hours, pay periods and a maximum balance.
Accrual per pay period
Choose weekly, biweekly, semi-monthly or monthly. The sheet builds the pay period calendar for 2027 and adds the accrual hours at the end of each period.
A cap that really stops accrual
Set a maximum balance. Once someone reaches it, accrual stops until they use some time. The sheet shows how many hours were not earned.
Running balance by period
See every person's balance at the end of every pay period, with hours used taken off and accrual added, never above the cap.
Projected balance at year end
Balance today and projected for 31 December, in hours and days, including time off you have already logged for later in the year.
Carryover limit, shown clearly
Set how many hours can carry into next year. The summary shows who will lose hours and how many, while there is still time to use them.
Overrides per person
Give someone their own accrual rate, their own cap, or a hire date so accrual starts later. Everyone else follows the policy on Setup.
How to use
Set your policy once, then log hours
Amber cells are for typing, grey cells are formulas.
Set your policy
On Setup, pick your pay frequency and the date the first pay period ends. Enter the accrual per pay period, the cap and the carryover limit.
Add your people
On Employees, add each person with the hours carried over. Use the overrides only when someone differs from the policy.
Log hours used
On Hours used, enter the date, the person and the hours. For a week off, enter one row per day or all the hours on the first day.
Read the summary
Year-end summary shows each balance today and on 31 December, the hours not earned because of the cap, and what carries over.
Clear the grey sample rows first: select the cells and press Delete. Please do not delete whole rows, so the formulas stay in place.
Under the hood
How the accrual works, with an example
How the accrual formula works
At the end of each pay period the sheet takes the balance after hours used and adds the accrual, but never lets the...
At the end of each pay period the sheet takes the balance after hours used and adds the accrual, but never lets the balance go above the cap. As a formula: new balance = MAX(x, MIN(cap, x + accrual)), where x is the previous balance minus the hours used in the period. A balance that is already above the cap simply stops accruing.
A person with a hire date accrues from the first pay period that ends on or after that date, at the full rate for that period. The Used by period sheet adds up the hours from your log for each pay period.
A worked example: the cap in action
Dana opens the year with 118 hours. The cap is 120 hours and she accrues 3.08 hours per biweekly period. After the...
Dana opens the year with 118 hours. The cap is 120 hours and she accrues 3.08 hours per biweekly period. After the first period she is at 120, not 121.08, and with no time off she stays there all year. She accrues 2 hours instead of 80.08, so 78.08 hours are never earned.
That is the use it or lose it pressure the Year-end summary makes visible. With a carryover limit of 80 hours she would also lose 40 hours on 31 December, so the sheet flags her early.
A note on US rules
Rules for accrual, caps, carryover and payout of unused PTO differ by state and by employer policy. Some states...
Rules for accrual, caps, carryover and payout of unused PTO differ by state and by employer policy. Some states treat earned vacation as wages and restrict use it or lose it policies, and some require payout when employment ends. The template calculates what you tell it to, so check your state rules and your own policy.
For one person at a time, try the PTO accrual calculator and the PTO payout calculator.
Honest limits
When a spreadsheet stops being enough
A spreadsheet works well for a small team with one simple policy. These are the points where it starts to cost more than it saves.
01
Policies that change
Tenure tiers, mid year rate changes and different rules by state turn into overrides that nobody remembers the reason for.
What LeaveCalc doesYou set the policy once for each group, and balances follow the rule.02
Requests and approvals
A request is an email or a message. Nobody sees what is pending, and the manager has to update the log by hand.
What LeaveCalc doesStaff ask through a link, you approve in one click, and the balance updates.03
Payroll and reporting
Payroll wants hours by pay period, and you rebuild that report from the log every cycle.
What LeaveCalc doesDownload a CSV of leave hours by person and leave type for any pay period.04
More than one editor
Two managers edit two copies, one formula gets overwritten, and a balance is wrong until someone notices.
What LeaveCalc doesOne place for everyone, with the same balance on every screen.
LeaveCalc for Employers keeps requests, approvals, balances and the team calendar in one place. It is free for up to 3 people, and Pro includes a 14-day trial. No card needed.
FAQ
Questions, answered plainly
You need a list of employees, an accrual rate per pay period, a log of hours used and a running balance. The free template above has all of them, plus a cap and a projected year end balance. Set your pay frequency and accrual on the Setup sheet, add your people, then log hours as they are taken.
Divide the annual PTO hours by the number of pay periods in the year. For 80 hours over 26 biweekly pay periods that is 3.08 hours per period. The template has a helper on Setup that does this sum, then adds the accrual to each balance at the end of every pay period.
Yes. Set a maximum balance on Setup, or give one person their own cap. When a balance reaches the cap, accrual stops until some hours are used. The Year-end summary shows how many hours each person did not earn because of the cap.
Yes. PTO and vacation work the same way here: hours that build up and get used. Use the Hours used sheet for vacation days, and if you track sick time separately, keep it in a second copy of the file or in the leave tracker template.
Set a carryover limit on Setup. The Year-end summary shows what each person carries into next year and how many hours are lost. When the new year starts, enter each person's carried over hours as the opening balance in a fresh copy of the file.
Yes. Go to File, Import, Upload, choose the .xlsx and pick Replace spreadsheet. Every function it uses exists in Google Sheets, and the drop-downs and colours come across.
A tracker records hours and you check the numbers. Software takes requests, approves them, runs accrual by your rules and keeps the calendar. A spreadsheet is fine for a small team, and PTO tracking software helps when the file becomes a chore.
Related tools and pages
Outgrown the spreadsheet?
Free for up to 3 people, Pro includes a 14-day trial, no card. Add your team, set your policies, share the link.