← Blog

PTO Tracker in Google Sheets for a Small Team

Build a PTO tracker in Google Sheets: log time off, count workdays used, see who is out on any day, and spot overlaps on a one-page 2027 team calendar.

On a small team, time off goes wrong in a predictable way. Two people book the same week in July, nobody notices until June, and someone has to cancel a trip or the team runs on one person.

A simple PTO tracker Google Sheets setup can catch that early. This post builds one in three tabs: a log of every request, a running total per person, and a day-by-day count of who's out, with the whole team's year shown on one page so overlaps are visible months ahead.

TL;DR

  • You get a PTO log, totals per person, a daily count of who's out, and a one-page team year.
  • Count time off in workdays with a holiday list, or a week off over a holiday overcharges someone.
  • The common mistake is tracking balances but never checking which days overlap.

Who this is for

  • Office Manager: Every request is in one sheet, and you can see who's out on any day without asking around.
  • HR Manager: Workdays used per person calculate themselves, holidays excluded.
  • Team Lead: Overlaps show up on the year view before you approve a request.
  • Small Business Owner: You can see the whole year of coverage without paying for an HR system.
  • Operations Manager: Busy seasons and time off sit on the same page, so you can plan staffing.

Tab 1: log every request

Make a tab called PTO with these columns:

  1. A: Name
  2. B: First day off
  3. C: Last day off
  4. D: Type (a dropdown from Data, Data validation, Dropdown: Vacation, Sick, Personal)
  5. E: Workdays: =NETWORKDAYS(B2,C2,HOLIDAYS!A:A)
  6. F: Approved (Insert, Checkbox)

Then a small HOLIDAYS tab with your company holidays in column A. If you follow the US federal schedule, OPM's 2027 list has 11 observed dates: Jan 1, Jan 18, Feb 15, May 31, Jun 18, Jul 5, Sep 6, Oct 11, Nov 11, Nov 25 and Dec 24. OPM also lists Friday, Dec 31, 2027 as the New Year's Day 2028 holiday, since Jan 1, 2028 is a Saturday.

NETWORKDAYS counts Monday to Friday, includes both the first and last day, and skips anything on the HOLIDAYS tab. Someone taking Monday Jul 5 to Friday Jul 9 uses 4 days, not 5, because July 5 is the observed Independence Day.

Tab 2: totals per person

Make a tab called TOTALS. Column A has each person's name, B their 2027 allowance in days, then:

  • C (Used): =SUMIF(PTO!A:A,A2,PTO!E:E)
  • D (Left): =B2-C2

For context when you set allowances: 2027 has 261 weekdays. Take out the 11 federal holidays and 250 are left, or 249 if you also give Dec 31 off.

Tab 3: who's out each day

This is the tab that catches overlaps. Make a tab called OUT. In A2:

=SEQUENCE(365,1,DATE(2027,1,1))

That lists every day of 2027. Format column A as dates. In B2:

=COUNTIFS(PTO!B:B,"<="&A2,PTO!C:C,">="&A2)

Fill it down to row 366. Each row now shows how many people are out that day. Select column B, open Format, Conditional formatting, and color cells red when the value is greater than or equal to 2 (or whatever number leaves you short). Scroll the red cells and you have every overlap in the year.

Put the team's year on one page

The OUT tab finds overlaps. To see them, I use Big Year, a free Google Sheet with all 12 months of 2027 on the BIG tab. Its license allows internal team use.

  1. Paste your company holidays into the SETTINGS tab. They highlight on BIG automatically.
  2. Give each person a fill color and type their initials on each day they're off.
  3. Share the file with the team as view only, and keep edit access for whoever approves time off.

Two people in the same week show up as two colors stacked in the same stretch of days, even on a printed copy. The DOWNLOAD tab makes a one-page PDF (Letter, A4, Tabloid or A3) for the office wall. Before approving any new request, check its dates on the OUT tab and on BIG, then tick the Approved box.

Common pitfalls

  • Counting calendar days: Use NETWORKDAYS with your holiday list so weekends and holidays aren't charged as PTO.
  • Typing names differently: SUMIF needs an exact match, so use a dropdown of names in the PTO tab's Name column.
  • Logging only approved time: Log requests too, with the checkbox unticked, so overlaps show before you say yes.
  • Forgetting half days: Add a column for partial days and subtract 0.5 in the Workdays formula when it's ticked.

FAQ

How do I track PTO in Google Sheets?

Log each request with a first and last day, count workdays with NETWORKDAYS and a holiday list, and total each person's days with SUMIF.

How do I count workdays between two dates in Google Sheets?

Use =NETWORKDAYS(start,end,holidays). It includes both dates and skips weekends and any holidays you list.

How many working days are in 2027?

2027 has 261 weekdays. After the 11 observed US federal holidays, 250 remain.

How do I see who is out on a specific day?

List every date and use COUNTIFS to count requests that start on or before the date and end on or after it.

Can I share the PTO calendar with my team?

Yes. Share the Google Sheet with view access for the team and edit access for the person who approves requests.

Planning next year? Get the Big Year, a free 2027 calendar in Google Sheets.

Keep reading

Ads keep the free stuff free. Members browse ad-free — log in or see pricing.
Big Year 2027 calendar: the whole year on one Google Sheet

Free 2027 calendar.Plan all of 2027 on one Google Sheet, free.

Copy it to your Google Drive.

Free. No password, no card. The sheet link shows up right here.