PTO and Leave Tracker Template for Excel: Accruals per Pay Period, Balances, Carryover Caps and Overlaps
Build a PTO and leave tracker in Excel or Google Sheets: accrual per biweekly pay period, used and planned hours, current balance, projected year-end balance against a carryover cap, and a who's-out-when overlap check, with a five-employee example.
A leave tracker template keeps one row per employee with their PTO accrual rate, hours carried over, hours accrued so far, hours used and hours already approved for the future; formulas turn those into a current balance, a projected year-end balance and a warning when someone is about to exceed your carryover cap. A second sheet logs every request with dates so you can see who is out on any given day. For a small business without an HR system, it answers “how much time off do I have?” in seconds and prevents December surprises.
This guide is part of Operations & HR. Pair it with the employee schedule template for shift coverage and the timesheet template for hours worked.
A quick word on the rules
In the United States, federal law does not require employers to offer paid vacation; PTO is usually a matter of company policy. Some things are regulated, though: eligible employees may be entitled to unpaid, job-protected leave under the FMLA, many states and cities require paid sick leave, and some states restrict “use-it-or-lose-it” vacation policies (California is a well-known example). Build the tracker around your written policy and check your state’s rules; this template organizes the numbers, it is not legal advice.
Sheet 1: Pay dates
List every pay date of the year in column A. For a biweekly schedule starting Friday, January 9, 2026: =SEQUENCE(26,1,DATE(2026,1,9),14). That produces 26 pay dates ending December 25, 2026. Accruing per pay period is how most payroll systems do it, so your sheet will match the pay stub.
Sheet 2: Employees and balances
| Column | Content | Formula |
|---|---|---|
| A – Employee | Name | — |
| B – Hire date | Start date | — |
| C – Annual PTO (hours) | Per your policy tier | — |
| D – Rate per pay period | Hours accrued each paycheck | =C2/26 |
| E – Carryover | Hours brought from last year | — |
| F – Accrued YTD | Pay dates after hire, up to today | =D2*COUNTIFS(PayDates,">"&B2,PayDates,"<="&TODAY()) |
| G – Used | Hours taken | =SUMIFS(Requests!E:E,Requests!A:A,A2,Requests!D:D,"Taken") |
| H – Balance | Available now | =E2+F2-G2 |
| I – Planned | Approved future hours | =SUMIFS(Requests!E:E,Requests!A:A,A2,Requests!D:D,"Approved") |
| J – Projected year-end | After remaining accruals | =H2+D2*COUNTIF(PayDates,">"&TODAY())-I2 |
| K – Hours at risk | Above the carryover cap in $M$1 | =MAX(0,J2-$M$1) |
Sheet 3: Requests
One row per request: employee (A), first day off (B), last day off (C), status (D: Requested, Approved, Taken, Denied) and hours (E). For full-day requests on an 8-hour schedule: =NETWORKDAYS(B2,C2,Holidays!A:A)*8. Partial days can be typed directly.
Worked example: five employees on November 6, 2026
Policy for the example: 120 hours a year for full-time staff, 160 hours after a tenure milestone, 60 hours for part-time staff, and a 40-hour carryover cap. By November 6, 22 of the 26 biweekly paychecks have been issued; four remain (November 13 and 27, December 11 and 25).
| Employee | Annual | Rate / period | Carryover | Accrued YTD | Used | Balance | Planned | Projected 12/31 | At risk |
|---|---|---|---|---|---|---|---|---|---|
| Alex | 120 h | 4.62 | 16 | 101.54 | 64 | 53.54 | 16 | 56.00 | 16.00 |
| Brianna | 120 h | 4.62 | 0 | 101.54 | 96 | 5.54 | 0 | 24.00 | 0 |
| Carlos | 160 h | 6.15 | 24 | 135.38 | 80 | 79.38 | 24 | 80.00 | 40.00 |
| Dana (part-time) | 60 h | 2.31 | 0 | 50.77 | 16 | 34.77 | 0 | 44.00 | 4.00 |
| Eli (hired 6/1/2026) | 120 h | 4.62 | 0 | 50.77 | 8 | 42.77 | 0 | 61.23 | 21.23 |
Eli only accrues from the first paycheck after his hire date, so 11 periods count instead of 22. Reading the last column: if nothing changes, about 81 hours across four people would exceed the 40-hour cap at year end, 40 of them Carlos’s. Under a forfeiture policy, that is time employees lose; where forfeiture isn’t allowed, it is a liability that rolls forward. Either way, the useful step now is to talk to Carlos, Eli and Alex about time off in December — and to check the hours against the next pay stub.
Who is out when
On a “Calendar” sheet, list each date in column A and count approved or taken leave:
=COUNTIFS(Requests!$B:$B,"<="&A2,Requests!$C:$C,">="&A2,Requests!$D:$D,"<>Denied",Requests!$D:$D,"<>Requested")
With your maximum simultaneous absences in N1: =IF(B2>$N$1,"Coverage risk",""). Before approving a new request, set it to Approved temporarily and check that no day turns red. December is where this matters most: when several people try to use their balances in the same two weeks, the calendar shows the clash before anyone books flights.
Good practices
- Write the policy down — accrual rate, tiers, cap, request notice — and make the sheet follow it exactly.
- Reconcile with payroll every month; the pay stub is the official record.
- Keep sensitive details out: log hours and dates, not medical reasons. Health information belongs in protected records.
- Separate leave types (vacation, sick, personal) with a Type column if your policy or local law treats them differently.
- Lock the formulas using the steps in how to protect formulas in Excel, and limit who can edit.
FAQ
Hours or days?
Hours work better when people take partial days or have different shift lengths. If everyone works fixed 8-hour days, divide by 8 for a days view.
What about front-loaded PTO?
If the full annual amount is granted on January 1, replace the accrual formula with the annual amount (prorated for new hires) and drop the pay-date count.
Does it work in Google Sheets?
Yes. SEQUENCE, COUNTIFS, SUMIFS and NETWORKDAYS all work, and you can share a view-only calendar with the team.
Next step: enter your pay dates and each employee’s hire date, annual hours and carryover, log this year’s requests, and check the “at risk” column before December schedules are set.