PTO tracker spreadsheet: how to track vacation and sick days

Updated 2026-10-20 · Plainsheet guides

Paid time off is easy to lose track of. Did that half day in March come out of vacation or sick leave? How many days carry over? Will the team be short-staffed the week before a holiday? A small PTO tracker spreadsheet answers all of this for one person or a small team, without HR software. This guide covers accruals, counting workdays properly, carryover, and a layout that stays easy to read.

Start with the policy

Before building anything, write the rules at the top of the sheet:

• Allowance: e.g. 20 vacation days and 5 sick days per year.
• Accrual: do you get the full amount on January 1, or does it build up each month or pay period (e.g. 20 ÷ 12 = 1.67 days per month)?
• Carryover: how many unused days roll into next year, and do they expire?
• Year type: calendar year, or based on hire date?
• Half days allowed?
• Public holidays that don't count as PTO.

Getting these written down ends most "how many days do I have?" arguments before they start.

The leave log

One row per absence: Person, Type (Vacation, Sick, Personal, Unpaid), Start date, End date, Half day?, Days used, Approved, Notes. Use dropdowns (data validation) for Person and Type to avoid typos that break the totals.

Counting workdays correctly

Don't subtract dates. A Friday-to-Monday absence is 2 workdays, not 4. Use NETWORKDAYS, which skips weekends and an optional holiday list:

=NETWORKDAYS(C2, D2, Holidays!A:A) - IF(E2="Yes", 0.5, 0)

Keep a Holidays tab with one date per row for this year and next. If your weekend isn't Saturday–Sunday, or you work a 4-day week, use NETWORKDAYS.INTL with a weekend pattern instead.

Balances per person

On a summary tab, list each person and work out:

• Accrued to date: for monthly accrual, =MIN(annual, annual/12 * months elapsed), or simply the annual figure if days are granted up front.
• Carried over from last year, capped at the policy maximum.
• Used: =SUMIFS(Log!F:F, Log!A:A, A2, Log!B:B, "Vacation")
• Remaining: accrued + carried over − used.
• Planned but not taken yet: approved future dates, so remaining doesn't look bigger than it is.

Do the same for sick days in separate columns. Most policies keep the two balances apart.

A team calendar view

For teams, a month grid with people as rows and days as columns makes overlaps obvious. Each cell checks whether that date falls inside any of the person's absences:

=IF(COUNTIFS(Log!A:A,$A2,Log!C:C,"<="&B$1,Log!D:D,">="&B$1)>0,"Off","")

Add conditional formatting so "Off" cells are colored, and shade weekends and holidays grey. Now you can see at a glance that three of five people are out the same week.

Carryover at year end

At the end of the year, copy each person's remaining balance, apply the carryover cap with =MIN(remaining, cap), and enter it in the next year's sheet. If carried-over days expire (say, by March 31), keep them in their own column so they're used first.

Good habits

• Log time off when it's approved, not after it's taken.
• Review balances every quarter. People who haven't taken leave in six months are a burnout warning sign.
• Remind people about use-it-or-lose-it days well before the deadline.
• Keep the sheet view-only for most people, with one editor, so the totals stay trustworthy.

Tracking just your own PTO?

The same structure works for one person. You get a log, a balance, and a calendar. It's especially handy for planning long weekends around public holidays: block in your holidays, then look for bridge days where one PTO day gives you four days off.

Template

The Plainsheet PTO & Leave Tracker ($8) tracks vacation and sick days for you or a small team. Workdays are counted automatically, skipping weekends and your holiday list. To plan the trips you're taking that PTO for, see the free monthly budget sheet, and for the rest of your week try the printable weekly planner ($5).

Leave rules vary by employer and country. Follow your official policy and local law.

FAQ

How do I count PTO days excluding weekends in Excel or Google Sheets?

Use NETWORKDAYS(start, end, holidays), which counts only weekdays and skips any dates in your holiday list. Subtract 0.5 for half days.

How do I calculate monthly PTO accrual?

Divide the annual allowance by 12 and multiply by the months worked so far, capped at the annual total. For example, 20 days a year accrues about 1.67 days a month.

Can I track PTO for a small team in one spreadsheet?

Yes. Keep one leave log for everyone, a summary tab with each person's balance using SUMIFS, and a month grid that highlights who is off each day.

Should sick days and vacation days be tracked separately?

Usually yes, because most policies give separate allowances and some laws treat them differently. Keep one leave log with a Type column, and calculate each balance with SUMIFS on that type. If your employer has a combined PTO bank instead, track one balance and use the Type column just for your own records.

How do I handle part-time employees in a PTO tracker?

Track leave in hours instead of days, and give each person a standard hours-per-day value. Multiply the workdays from NETWORKDAYS by that value to get hours used. Accrual is often a fixed number of hours earned per hour worked, which you can calculate from a timesheet column.

What is a PTO carryover cap?

It's the maximum number of unused days that can roll over into the next year. If the cap is 5 days and you have 8 left on December 31, only 5 carry over, and the rest are lost or, under some policies, paid out. Use MIN(remaining, cap) in the spreadsheet to calculate it.

How can I get the most days off from my PTO?

Put your public holidays on a calendar first, then look for "bridge" days between a holiday and a weekend. One PTO day next to a Thursday or Tuesday holiday can give you a four-day weekend, and a few days around a holiday week can turn into nine or ten days off in a row.

← All guides