How to track PTO in Google Sheets

On this page: The tabs · 8 steps · Formulas · FAQ · Ready-made template

The tabs you need

The rule that keeps a PTO spreadsheet accurate: the Leave Log is the only place anyone types leave. Balances that are edited by hand next to a log drift out of date within weeks.

8 steps to a PTO tracker that stays correct

Step 1: Set up a Settings tab

Enter the leave year, your weekend pattern (Sat–Sun, Fri–Sat, etc.), hours per working day, and a Holidays list with your public holidays and company closure days.

Step 2: List employees and allowances

One row per person: name, team, start date (and end date if they left), annual PTO allowance, sick-day allowance, accrual method (upfront or monthly) and any carryover from last year.

Step 3: Log every request once

Add one row per request on a Leave Log tab: employee, leave type, start date, end date, half-day option and status (Pending, Approved, Denied, Cancelled). Use dropdowns so names and types can't be mistyped.

Step 4: Count working days automatically

Calculate days per request with NETWORKDAYS.INTL(start, end, weekend_code, Holidays) so weekends and holidays don't count, then subtract 0.5 for half days.

Step 5: Calculate balances with SUMIFS

On a Balances tab, sum approved days per person and leave type with SUMIFS. Remaining = allowance + carryover − approved. Also show Remaining if pending is approved = Remaining − pending.

Step 6: Check for clashes before approving

Use COUNTIFS to count other approved requests that overlap the same dates (optionally within the same team), and flag the row when the count is above how many people can be off at once.

Step 7: Collect requests with a Google Form

Link a Google Form (name, type, dates, note) to the sheet so staff requests land on a Requests tab, then approve or deny them there.

Step 8: Roll over to the next year

Copy the file, change the leave year, and paste each person's capped carryover: MIN(unused days, carryover cap).

The formulas

Assumes a Leave Log with Employee in column A, Type in B, Start in C, End in D, Days in F and Status in G.

Working days in a request (Saturday–Sunday weekend, holidays in Holidays!A2:A100)

=IF(OR(C2="",D2=""),"",NETWORKDAYS.INTL(C2,D2,1,Holidays!$A$2:$A$100))

Approved PTO days for the person in A2 of the Balances tab

=SUMIFS(Log!$F:$F,Log!$A:$A,A2,Log!$G:$G,"Approved",Log!$B:$B,"Vacation")

Clash count: other approved requests overlapping row 2's dates

=COUNTIFS(Log!$A:$A,"<>"&A2,Log!$C:$C,"<="&D2,Log!$D:$D,">="&C2,Log!$G:$G,"Approved")

Pro-rated allowance for someone who joins mid-year (start date in C2, annual allowance in D2)

=ROUND(D2*(13-MONTH(C2))/12*2,0)/2

Monthly accrual earned to date (1.25 days a month = 15 a year)

=MIN(D2,1.25*(MONTH(TODAY())-1))

Weekend codes for NETWORKDAYS.INTL: 1 = Saturday–Sunday, 7 = Friday–Saturday, 11 = Sunday only, 17 = Saturday only.

FAQ

What is the easiest way to track employee PTO for a small business?

For a team of roughly 5–50 people, a spreadsheet with one Leave Log, formula-driven balances and a shared calendar is usually enough. Log each request once, let formulas calculate balances, and move to HR software when you need payroll sync, mobile apps or private per-employee views.

Can Google Sheets calculate PTO balances automatically?

Yes. Use SUMIFS over the Leave Log to total approved (and separately pending) days per employee, and subtract them from the allowance plus carryover. Because balances are calculated rather than typed, they can't drift out of date.

How do I count PTO days without weekends and holidays?

Use NETWORKDAYS.INTL(start, end, weekend, holidays). The weekend argument handles Saturday–Sunday (1), Friday–Saturday (7) or Sunday-only (11) weekends, and the holidays range removes public holidays and closure days.

How do I calculate PTO accrual in a spreadsheet?

For monthly accrual, multiply the monthly rate by months completed, capped at the annual allowance, e.g. 15 days a year accrues 1.25 days per month. For mid-year starters, pro-rate the allowance by the months they'll work. Hourly accrual (per hour worked) needs a timesheet ledger and is beyond a simple tracker.

How do I handle half days?

Add a half-day column (none, single half day, half first day, half last day) and subtract 0.5 for each half from the working-day count.

How do I stop two people being off at the same time?

Add a clash check: COUNTIFS the approved requests whose start is on or before this request's end and whose end is on or after its start. Flag the request when the count reaches your limit for that team.

Can employees request time off through Google Forms?

Yes. Create a Google Form with name, leave type, start date, end date and a note, link responses to the sheet, and review them on a Requests tab before copying approved ones into the Leave Log.

Is there a free PTO tracker template for Google Sheets?

Yes, several free templates exist and work well for basic balances. Paid templates such as the Hazelshelf Leave & PTO Tracker ($14) add things like clash warnings, a Google Form request flow, accrual with pro-rating, and public holidays for 2026–2028 already filled in.

Does a PTO spreadsheet work in Excel too?

If it uses only standard formulas (SUMIFS, COUNTIFS, NETWORKDAYS.INTL, INDEX/MATCH) and no macros, the same .xlsx works in Excel on Windows, Mac and the web, and in Google Sheets.

Skip the build: the Hazelshelf Leave & PTO Tracker

Need something custom (rota, job quoting, client tracker)? Hazel Studio builds one-off Google Sheets systems.