Free vacation tracker spreadsheet template (Google Sheets & Excel)

Updated October 2026 · About 6 minutes to read

If you need to know who's off and how many vacation days each person has left, a spreadsheet does the job. You can download our free template (up to 5 employees, no sign-up beyond Gumroad's free checkout), or build your own in about 15 minutes with the layout and formulas below.

Formulas use commas between arguments (US English default). If your spreadsheet locale uses semicolons, swap them. Every formula on this page was checked in a test spreadsheet; the example numbers below are the results it produced.

What a good vacation tracker needs

A request log: one row per request, not one tab per person. It's much easier to filter and total.
Automatic day counts that skip weekends and company holidays.
A status column (Pending / Approved / Denied) so only approved time comes off the balance.
A balance per person: allowance, taken, pending and remaining.
A "who's off" check for any date, so you can spot coverage gaps.

Step 1: Requests tab

Columns: A Employee, B First day off, C Last day off, D Status, E Days. Put your holiday dates in a range such as H20:H22 (any column or tab works). In the Days column:

=IF(OR(B20="",C20=""),"",NETWORKDAYS(B20,C20,$H$20:$H$22))

Example: Nov 23–27, 2026 with Thanksgiving (Nov 26) in the holiday list counts as 4 days. Dec 24–28 with Christmas listed counts as 2 (the weekend and the holiday are skipped). Use Data → Data validation to make Status a dropdown.

Step 2: Balances tab

Keep this on its own tab (or at least outside the request columns). Otherwise the totals can end up adding themselves, which causes a circular-reference error. Column K is the name, L the annual allowance. Days taken (approved only):

=SUMIFS($E$20:$E$100,$A$20:$A$100,K26,$D$20:$D$100,"Approved")

Days remaining:

=L26-M26

Days pending approval:

=SUMIFS($E$20:$E$100,$A$20:$A$100,K26,$D$20:$D$100,"Pending")

With a 15-day allowance, 4 approved days and 2 pending, that gives taken 4, remaining 11, pending 2. On a separate tab, prefix the ranges with the tab name, e.g. Requests!$E$20:$E$100.

Step 3: Is someone off on a given date?

=COUNTIFS($A$20:$A$100,K26,$B$20:$B$100,"<="&DATE(2026,11,24),$C$20:$C$100,">="&DATE(2026,11,24),$D$20:$D$100,"Approved")>0

This returns TRUE when the person has an approved request covering Nov 24. Replace the DATE(...) with a cell reference, copy it across a row of dates, and you've got a team calendar. Add conditional formatting to color the TRUE cells.

Want accrual instead of a fixed allowance?

If time off is earned over the year instead of granted up front, see How to calculate PTO accrual for per-pay-period, monthly and daily formulas.

Get the template

The free Lite version of Simple PTO Tracker has all of this already built: settings, employee roster, request log with dropdowns, automatic balances with accrual and carryover caps, and a monthly calendar. Up to 5 employees, in Google Sheets or Excel. Need more people? The full version ($14) supports 100 employees.

Not legal advice. Leave rules vary by state and sometimes by city (for example required paid sick leave, limits on "use it or lose it", and payout of unused vacation when someone leaves). Check the rules that apply to you before setting a policy.