PTO Tracking Spreadsheet

PTO Tracking Spreadsheet Template

Tracking paid time off in a spreadsheet is the starting point for most small businesses. It is free, flexible, and familiar. This PTO tracking spreadsheet template gives you a ready-to-use column structure, formula guidance, and sample data so you can start tracking balances, requests, and accruals right away.

If you are managing fewer than 15 employees and your PTO policy is straightforward, a well-organized spreadsheet can work just fine -- as long as you keep it updated.

When to Use This Template

When you are setting up PTO tracking for the first time and need a simple starting point
When you want to replace an informal system (sticky notes, email approvals, mental math) with something organized
When your team is small enough that a spreadsheet is still manageable
When you need a backup or audit trail alongside your payroll system's PTO data

This template works in Google Sheets, Microsoft Excel, or any spreadsheet app that supports basic formulas. Copy the structure below into your preferred tool and adjust as needed.

Spreadsheet Structure

Tab 1: Employee PTO Summary

This is your master view. One row per employee, showing their annual allotment, usage, and remaining balance at a glance.

Column	Header	Description	Example
A	Employee Name	Full name	Jane Smith
B	Department	Team or department	Marketing
C	Start Date	Hire date (used for accrual calculations)	03/15/2024
D	PTO Policy	Which policy tier applies	Standard (15 days/yr)
E	Annual Allotment	Total PTO days available for the year	15
F	Carryover from Prior Year	Days rolled over (if your policy allows)	2
G	Total Available	Column E + Column F	17
H	Days Used YTD	Running total of PTO taken this year	6
I	Days Scheduled	Approved future PTO not yet taken	3
J	Remaining Balance	Column G - Column H - Column I	8
K	Notes	Special circumstances, LOA, adjustments	Used 1 day unpaid in March

Key Formula:

Total Available (G): =E2+F2
Remaining Balance (J): =G2-H2-I2

Tab 2: PTO Request Log

This tab records every individual PTO request. Each row is a single request, which feeds the summary totals on Tab 1.

Column	Header	Description	Example
A	Employee Name	Who is requesting time off	Jane Smith
B	Request Date	Date the request was submitted	01/10/2026
C	PTO Start Date	First day of the time off	02/14/2026
D	PTO End Date	Last day of the time off	02/16/2026
E	Total Days	Number of PTO days in this request	2
F	Type	Vacation, Sick, Personal, Bereavement, etc.	Vacation
G	Status	Pending, Approved, Denied, Cancelled	Approved
H	Approved By	Manager or owner who approved	Mark Johnson
I	Date Approved	When the approval was given	01/12/2026
J	Notes	Any additional context	Presidents Day weekend

Key Formula for Tab 1 "Days Used YTD" (H):

Use a SUMIFS formula on Tab 1 to pull totals from Tab 2:

=SUMIFS('PTO Request Log'!E:E, 'PTO Request Log'!A:A, A2, 'PTO Request Log'!G:G, "Approved", 'PTO Request Log'!C:C, "<="&TODAY())

This sums the total approved PTO days for the employee where the start date has already passed.

Key Formula for Tab 1 "Days Scheduled" (I):

=SUMIFS('PTO Request Log'!E:E, 'PTO Request Log'!A:A, A2, 'PTO Request Log'!G:G, "Approved", 'PTO Request Log'!C:C, ">"&TODAY())

This sums the total approved PTO days where the start date is in the future.

Tab 3: Monthly Calendar View (Optional)

A visual calendar helps you see at a glance who is out on any given day. Set it up as follows:

	Tue 2/3	Wed 2/4	Thu 2/5	Fri 2/6
Jane Smith		PTO	PTO
Mark Johnson				PTO
Sarah Lee	PTO	PTO	PTO
Tom Davis

Tips for the calendar view:

Create one tab per month, or use a single scrolling tab with conditional formatting
Color-code by leave type (green for vacation, yellow for sick, blue for personal)
Use data validation dropdowns in each cell (PTO, Sick, Personal, blank) to keep entries consistent

Tab 4: Accrual Tracker (If Applicable)

If your PTO policy uses accrual (employees earn time off each pay period rather than receiving it all at once), add an accrual tab.

Column	Header	Description	Example
A	Employee Name	Full name	Jane Smith
B	Accrual Rate	Days or hours earned per pay period	0.577 days/period
C	Pay Period End Date	End of the pay period	01/15/2026
D	Hours/Days Accrued	Amount earned this period	0.577
E	Cumulative Accrued YTD	Running total for the year	1.154
F	Used YTD	Total taken so far	0
G	Available Balance	Column E - Column F + carryover	3.154

Accrual Rate Formula:

If an employee earns 15 days per year and you run 26 biweekly pay periods:

=15/26 = approximately 0.577 days per pay period

Sample Data: Employee PTO Summary

Here is what your completed summary tab might look like mid-year:

Employee Name	Dept	Start Date	Policy	Annual	Carryover	Total	Used	Scheduled	Remaining
Jane Smith	Marketing	03/15/2024	Standard	15	2	17	6	3	8
Mark Johnson	Sales	01/08/2025	Standard	15	0	15	4	0	11
Sarah Lee	Operations	06/01/2023	Senior	20	5	25	10	2	13
Tom Davis	Engineering	11/12/2025	Standard	15	0	15	1	0	14

How to Use This Template

Copy the structure into your spreadsheet tool. Create a new Google Sheet or Excel workbook and set up the tabs described above. Start with Tab 1 and Tab 2 at minimum.
Enter your team. Add a row to the summary tab for each employee. Fill in their hire date, policy tier, and annual allotment.
Set the formulas. Enter the SUMIFS and balance formulas so your summary tab updates automatically as you log requests.
Log every request on Tab 2. When an employee asks for time off, add a row to the request log. Update the status when it is approved or denied.
Review balances monthly. Check the summary tab at least once a month to catch errors, confirm balances match payroll, and flag employees running low on PTO.
Archive at year end. Save a copy of the current year's spreadsheet, then reset balances for the new year. Carry over any rollover days per your policy.

Spreadsheets are only as accurate as the data you put in. If you forget to log a request or update a status, your balances will be wrong. Set a recurring reminder to review the tracker each pay period.

Tips for Small Businesses

Keep it in a shared location. Store the spreadsheet in Google Drive or SharePoint so managers can access it. Use sheet-level permissions to control who can edit vs. view.
Lock the formula cells. Protect the cells that contain formulas so nobody accidentally overwrites them.
Back it up. Spreadsheets can be deleted or corrupted. Keep periodic backups.
Be consistent with leave types. Use a dropdown list for the "Type" column (Vacation, Sick, Personal, etc.) so entries are uniform and your formulas work correctly.

When You Have Outgrown Spreadsheets

Spreadsheets work well when your team is small and your PTO policy is simple. But as your business grows, the manual data entry, formula maintenance, and version-control headaches start to add up. If you are spending more time managing the spreadsheet than actually managing your team, it might be time to look at a dedicated tool. Boring HR's Team Tracker gives small businesses a single place to manage employee records, time off, and certifications -- no formulas required.