Excel PTO tracker: free vacation and leave tracker template
A PTO tracker answers three questions at a glance: who is off when, how many days has each person used, and how many are left. Here is a free Excel template for a team of up to 20, and a guide to every formula in it.
In short: log each absence as one row with name, from, to and type. =NETWORKDAYS(From,To,Holidays) counts the PTO days without weekends and holidays, SUMIFS adds them up per person, and days left are the allowance minus days taken. A year view built with COUNTIFS shows every day as V (vacation) or S (sick).
Try it now
The number everyone asks about is the balance. In the simplest tracker, vacation days are marked V in the day columns and you count them.
Task In K2, calculate the days left: the allowance in B minus the days marked V for vacation in the plan C2:J2. S for sick does not count. The formula is filled down to K6.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Allowance | 1/11/2027 | 1/12/2027 | 1/13/2027 | 1/14/2027 | 1/15/2027 | 1/16/2027 | 1/17/2027 | 1/18/2027 | Left |
| 2 | Olivia Brown | 30 | V | V | V | ||||||
| 3 | James Smith | 28 | V | V | |||||||
| 4 | Emma Wilson | 30 | S | ||||||||
| 5 | Noah Taylor | 26 | V | V | V | V | V | V | |||
| 6 | Ava Johnson | 30 | V | V |
Download the PTO tracker template
The file is set to 2027; change the year in B2 and it works for any year. There is room for 20 people and 200 time-off entries, and the holidays can be US federal holidays, UK bank holidays (England and Wales) or none.
Enlarge previewDownload the finished template
The finished Excel file pto-tracker.xlsx, with sample rows and instructions, is free. All you need is a free account: no subscription, no payment details.
Create a free account and download the template Already have an account? Log in
Need a plain calendar without people? Use the Excel calendar template.
How the tracker is laid out
| Sheet | What it holds | Formula behind it |
|---|---|---|
| PTO tracker | Year in B2, holidays in D2; allowance, taken and left per person; every day of the year to the right | =B7-C7 for days left |
| Time off | A table with name (drop-down), from, to, type (Vacation or Sick), workdays and a note | =NETWORKDAYS(B5,C5,Holidays) |
| Holidays | US and UK holidays for the year, recalculated for any year, plus your own company days off | Rules like the last Monday in May |
| Instructions | A short version of this guide |
The key idea: you never type into the day columns. The list of absences is the only input, and the year view fills itself from it. Nothing can contradict anything else, and an approved request is exactly one row.
Counting PTO days with NETWORKDAYS
Leave from one Monday to the Friday of the following week spans twelve calendar days but uses only ten PTO days, and fewer if a holiday falls inside. NETWORKDAYS counts exactly that: Monday to Friday between two dates, both included, minus the dates in a holiday list.
Task In D2, calculate how many PTO days the request uses: workdays from B to C without weekends and without the holidays in G2:G3. The formula is filled down to D6.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | From | To | Workdays | Holidays | ||
| 2 | Olivia Brown | 3/22/2027 | 3/31/2027 | 3/26/2027 | |||
| 3 | James Smith | 3/1/2027 | 3/5/2027 | 3/29/2027 | |||
| 4 | Emma Wilson | 3/29/2027 | 4/2/2027 | ||||
| 5 | Noah Taylor | 4/19/2027 | 5/3/2027 | ||||
| 6 | Ava Johnson | 4/7/2027 | 4/8/2027 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
In the template, the third argument points to the name Holidays. Behind it is a column that only contains the holidays you picked in D2, so switching from US to UK changes every count at once. For a half day, overwrite the workdays in that row with 0.5.
Allowance, taken and days left
SUMIFS adds the workdays of every row where the name and type match and the leave starts in the selected year:
=SUMIFS(TimeOff[Workdays],TimeOff[Name],$A7,TimeOff[Type],"Vacation",TimeOff[From],">="&DATE($B$2,1,1),TimeOff[From],"<="&DATE($B$2,12,31))
The column names work because the time-off list is an Excel table, so new rows count automatically. Sick days show up in the plan but do not come off the allowance. Days left are =B7-C7, and a rule turns them red below zero. Carry-over from last year? Add it to the allowance.
The year view: who is out when?
To the right of the names, every day of the year has its own column. Row 5 starts with =DATE($B$2,1,1) and each column adds 1. In a leap year the 366th column is filled, otherwise it stays blank.
Each cell of the plan uses COUNTIFS to check whether that person has a time-off row whose From is on or before the day and whose To is on or after it. A Vacation row shows V, a Sick row shows S, and weekends and holidays stay blank.
The colors come from conditional formatting. Notice the direction: the dates run across row 5, so the rule has to lock the row and let the column move.
Task The plan shows the days side by side in row 1. Use conditional formatting on B1:I6 to shade whole columns grey when the date in row 1 falls on a weekend. Write the formula as it applies to B1.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | 1/11/2027 | 1/12/2027 | 1/13/2027 | 1/14/2027 | 1/15/2027 | 1/16/2027 | 1/17/2027 | 1/18/2027 |
| 2 | Olivia Brown | V | V | V | V | V | V | V | |
| 3 | James Smith | ||||||||
| 4 | Emma Wilson | S | S | ||||||
| 5 | Noah Taylor | V | V | V | |||||
| 6 | Ava Johnson |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
Annual leave tracker, vacation tracker, PTO: same sheet
In the US, paid time off often bundles vacation and personal days, and some companies accrue it per pay period. In the UK the same file is usually called an annual leave tracker or holiday tracker, and the allowance includes or excludes bank holidays depending on the contract. The template handles both by keeping things simple: you enter the allowance per person, and every vacation day that is not a weekend or a selected holiday counts against it.
- Accrual: if PTO builds up over the year, replace the allowance with a formula such as
=Rate*MONTH(TODAY())for a monthly rate. - Overlaps: add a row below the plan with
=COUNTIF(E7:E26,"V"), fill it right, and you see how many people are out each day. COUNTIF is all it takes. - Names from a list: on the Time off sheet the name comes from a drop-down list, so a typo cannot drop a row from the totals.
- Privacy: sick days are health information. Share a file with that column only with the people who need it, or drop the Sick type.
Common mistakes
To before From
If the end date is before the start date, NETWORKDAYS returns a negative number and the balance grows. The template shades such rows red.
A holiday list without dollar signs
Without a locked reference, the holiday range slides down as you fill. A name like Holidays avoids that.
Leave across New Year
Time off from December 28 to January 8 counts in full in the year it starts. To split it, enter two rows: up to December 31 and from January 1.
PTO formulas in German Excel
| English | German |
|---|---|
| NETWORKDAYS | NETTOARBEITSTAGE |
| COUNTIF, COUNTIFS | ZÄHLENWENN, ZÄHLENWENNS |
| SUMIFS, WEEKDAY, DATE | SUMMEWENNS, WOCHENTAG, DATUM |
Which Excel versions support this?
NETWORKDAYS, COUNTIFS, SUMIFS, Excel tables and conditional formatting work in every current version of Excel, including Excel for Mac and Excel for the web. The template does not need any Microsoft 365 functions.
FAQ
Is the PTO tracker template free?
Yes, with a free account. No subscription and no payment details.
How many employees does it handle?
20 people and 200 time-off entries. For more people, insert rows above row 26 in the plan and copy the formulas from the row above.
Does it track sick days separately?
Yes. Choose Sick as the type and the day shows S in the plan without reducing the vacation balance.
Can I use it for UK annual leave?
Yes. Pick UK in D2 and the bank holidays for England and Wales are excluded from the count. Enter the allowance per person the way your contracts define it.
Can I track half days?
Enter the day as usual and overwrite the workdays in that row with 0.5. Taken and left use that number.
More files are on the Excel templates page.