How to Build a PTO Tracker in Google Sheets (Step by Step)

A Google Sheets PTO tracker is the right answer for a lot of small teams. It is free, everyone can see it, and it takes about twenty minutes to build.

This guide walks through building one properly — with formulas that survive contact with real use — and is honest about where it stops working.

If you would rather skip the building, the same structure is available as a ready-made file. Download it, then in Google Sheets choose File → Import → Upload. Formulas and dropdowns come across intact.

The structure

Three tabs, plus one for holidays. Resist adding more; every extra tab is another thing to keep in sync.

The critical idea is that the log is the only place data is entered, and balances are derived from it. The most common broken spreadsheet is one where someone maintains a balance column by hand alongside a request log, and the two drift apart within a month.

Step 1: the Employees tab

Columns A–F:

Enter one row per person. Keep names exactly consistent — the formulas match on the text, so "Sam Okafor" and "Sam O." are two different people as far as Sheets is concerned.

Step 2: the Time off log

Columns A–G:

Column E is calculated. In E2:

=IF(OR(C2="",D2=""),"",NETWORKDAYS(C2,D2,Holidays!$A$2:$A$60))

NETWORKDAYS counts weekdays inclusively and subtracts any date found in the holiday range. So a request from Monday to Friday that contains one public holiday returns 4, which is exactly what should come off the balance.

Fill that formula down 200 rows now so future entries calculate themselves.

Add dropdowns to columns B and F:

• Type: Vacation, Sick, Personal, Unpaid, Other
• Status: Pending, Approved, Denied

If your team does not work Monday to Friday, swap in NETWORKDAYS.INTL, whose third argument sets the weekend pattern. NETWORKDAYS.INTL(C2,D2,7,Holidays!$A$2:$A$60) treats Friday and Saturday as the weekend, for example.

Step 3: the Balances tab

This is where the tracker earns its keep. Columns A–F, all formulas:

The distinction between Remaining and Available is the one most homemade trackers miss. Remaining counts approved time only. Available also subtracts requests you have not decided on yet. When you are approving a request, Available is the number to look at — otherwise you can approve two requests that individually fit the balance and together do not.

Fill all six columns down as far as you have employees, plus some spare rows.

Step 4: make it readable

Two pieces of conditional formatting are worth the two minutes:

Highlight low balances. Select F2:F60 → Format → Conditional formatting → "Less than" → 0 → red fill. Negative available balance means you have over-approved.

Highlight pending rows. Select the log's A2:G200 → Conditional formatting → Custom formula → =$F2="Pending" → amber fill. Now anything awaiting a decision is visible at a glance, which is the closest a spreadsheet gets to a notification.

Freeze the header row on every tab (View → Freeze → 1 row) and protect the Balances tab (Data → Protect sheet) so nobody types over a formula.

Step 5: sharing it

Share the file with Viewer access for the team and Editor for whoever administers it. Do not give everyone edit rights — the single fastest way to destroy a shared tracker is two people editing the same row, and Sheets will not warn you.

If you want employees to submit requests themselves, add a Google Form that writes into the log. Form → link to the log sheet, fields matching columns A–D and G. Status stays blank until an admin sets it.

What this cannot do

Being clear-eyed about the ceiling saves you from discovering it during a bad week.

• Nothing notifies anyone. Requests sit in the sheet until somebody opens it. This is the failure that actually costs you — an employee who asked three weeks ago and heard nothing.
• No approval trail. Changing "Pending" to "Approved" leaves no record of who did it or when. Version history technically holds it; nobody has ever successfully used version history to settle a dispute.
• Concurrent edits overwrite. Two admins on a Monday morning, and one of them loses their entry with no error message.
• No overlap warning. Nothing tells you that approving this request leaves a team of four with one person in the office.
• Balances only look right. A SUMIFS with a mistyped name silently returns zero. The balance is confidently wrong, and it looks exactly like a balance that is right.
• Accrual is hard. Everything above assumes a fixed annual allowance. Per-pay-period accrual with caps is possible in Sheets but fragile — see PTO accrual per pay period for the arithmetic you would be reimplementing.

Why spreadsheets break for PTO covers these failure modes in more depth, and ways to track PTO compared puts spreadsheets alongside the alternatives.

Useful additions

If you are staying in Sheets, three additions pay for themselves:

A "who's out this week" cell. Somewhere prominent:

=TEXTJOIN(", ",TRUE,FILTER('Time off log'!A2:A,'Time off log'!C2:C<=TODAY()+7,'Time off log'!D2:D>=TODAY(),'Time off log'!F2:F="Approved"))

A team total. =SUM(Balances!E2:E60) tells you the total unused liability sitting on the books. The PTO cost calculator converts that into money.

A year-end check. Before December, sort Balances by Remaining descending. The people at the top are the ones who will either lose time or dump three weeks on you in the last fortnight.

When to stop

The honest signal is not team size, it is any one of these three:

1. More than one person needs to edit it.
2. A request went unanswered because nothing told anyone it existed.
3. Someone found an error in their own balance.

Any of those and the spreadsheet has started costing more than it saves.

When you get there, you do not have to start over. SimplyPTO imports this exact structure — upload the file and your Employees tab becomes a working team with allowances and carryover intact, and everything the spreadsheet could not do (notifications, an approval trail, overlap warnings, enforced caps) is there from the first day. Free for up to 10 people.