How to track PTO in Excel (free template + formulas)

Updated October 2026 · About 8 minutes to read

For a small team, Excel is all you need to track paid time off: a log of requests, a balance for each person and a calendar of who's out. This guide builds all three using Excel's own features (tables, named ranges, data validation and conditional formatting), with a worked example near the end and a free ready-made template at the bottom.

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

You'll build four sheets:

Settings: year dates, company holidays and leave types
Requests: one row per time-off request
Employees: one row per person, with their balance
Calendar: a monthly who's-out grid

Step 1: Settings sheet and named ranges

Cell	What goes in it	Example
B1	Year start (calendar or fiscal year)	2026-01-01
B2	As-of date	`=TODAY()`
B3	Year end	`=EDATE(B1,12)-1`
D2:D20	Company holiday dates	2026-01-01, 2026-05-25, ...
F2:G10	Leave type, and whether it counts as PTO	Vacation / Yes, Sick / No

Now give the lists names. Select D2:D20, click the Name Box (the box left of the formula bar), type Holidays and press Enter. Name F2:G10 LeaveTypes the same way. You can edit names later under Formulas > Name Manager.

If B3 shows a number like 46387, it's a date without a date format. Press Ctrl+1 and choose Date.

Step 2: Requests sheet as an Excel table

On a sheet named Requests, type these headers in A1:I1:

A	B	C	D	E	F	G	H	I
Employee	Type	First day	Last day	Half day	Override	Days	Status	Counts as PTO

Click any header cell and press Ctrl+T (or Insert > Table), tick My table has headers, then rename the table tblRequests on the Table Design tab. Tables copy formulas into new rows automatically.

In G2, count the working days in the request:

=IF(OR(C2="",D2=""),"",IF(F2<>"",F2,NETWORKDAYS(C2,D2,Holidays)-IF(E2="Yes",0.5,0)))

NETWORKDAYS counts Monday to Friday between two dates, including both ends, and skips anything in Holidays. Setting Half day to Yes takes half a day off the total, so a one-day request with Half day = Yes counts as 0.5. Type a number in Override when the count doesn't fit, such as for a three-day-a-week schedule.

In I2, look up whether the leave type comes out of the PTO balance:

=IF(B2="","",IFERROR(VLOOKUP(B2,LeaveTypes,2,FALSE),"No"))

On Excel 2021 or Microsoft 365, XLOOKUP works too:

=IF(B2="","",XLOOKUP(B2,Settings!$F$2:$F$10,Settings!$G$2:$G$10,"No"))

Open on Saturdays? NETWORKDAYS.INTL takes a weekend code, Monday first, 1 = day off. "0000001" means only Sunday is off:

=NETWORKDAYS.INTL(C2,D2,"0000001",Holidays)

Step 3: Dropdowns and date checks

Name the employee list first: on the Employees sheet (Step 4), select A2:A101 and name it EmployeeNames. Then on the Requests sheet use Data > Data Validation, set Allow to List, and enter a source for each column:

Employee: =EmployeeNames
Type: =Settings!$F$2:$F$10
Status: Pending,Approved,Denied,Cancelled
Half day: Yes,No

Dropdowns matter: "Dana Brooks" and "Dana Brooks " (trailing space) are different names to SUMIFS.

For the Last day column, choose Allow: Custom with D2 as the active cell, and use this formula so Excel rejects an end date before the start date:

=D2>=C2

Pasted values skip validation, so also select the table rows, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter this with a red fill:

=AND($C2<>"",$D2<$C2)

Reversed dates don't cause an error: NETWORKDAYS from Aug 14 back to Aug 10 returns −5, which would quietly add days to a balance.

Step 4: Employees sheet and balances

Headers in A1:J1: Name, Start date, Annual days, Unused last year, Carryover cap, Carryover, Accrued, Taken, Pending, Available. A to E are inputs; F to J are formulas. Make this a table too (tblEmployees).

F2, carryover limited to the cap:

=MIN(D2,E2)

G2, accrued so far this year, spread evenly across the days of the year (new hires are prorated from their start date):

=ROUND(C2*MAX(0,MIN(Settings!$B$2,Settings!$B$3)-MAX(B2,Settings!$B$1)+1)/(Settings!$B$3-Settings!$B$1+1),2)

For monthly, per-paycheck or hourly accrual, see How to calculate PTO accrual.

H2, approved PTO with a first day in this year:

=SUMIFS(Requests!$G:$G,Requests!$A:$A,A2,Requests!$H:$H,"Approved",Requests!$I:$I,"Yes",Requests!$C:$C,">="&Settings!$B$1,Requests!$C:$C,"<="&Settings!$B$3)

SUMIFS adds Days only for rows matching every condition: this person, Approved, counts as PTO, first day in this year. I2 is the same with "Pending":

=SUMIFS(Requests!$G:$G,Requests!$A:$A,A2,Requests!$H:$H,"Pending",Requests!$I:$I,"Yes",Requests!$C:$C,">="&Settings!$B$1,Requests!$C:$C,"<="&Settings!$B$3)

J2, available now:

=F2+G2-H2

Because both sheets are tables, H2 can use structured references instead, which keep working if someone inserts a column:

=SUMIFS(tblRequests[Days],tblRequests[Employee],[@Name],tblRequests[Status],"Approved",tblRequests[Counts as PTO],"Yes",tblRequests[First day],">="&Settings!$B$1,tblRequests[First day],"<="&Settings!$B$3)

If sick days are tracked separately from PTO, this totals approved sick days for the year (for a full sick balance earned per hour worked, see How to track employee sick days in a spreadsheet):

=SUMIFS(Requests!$G:$G,Requests!$A:$A,A2,Requests!$B:$B,"Sick",Requests!$H:$H,"Approved",Requests!$C:$C,">="&Settings!$B$1,Requests!$C:$C,"<="&Settings!$B$3)

If your policy is written in hours and a workday is 8 hours, show the balance in hours with:

=J2*8

To flag negative balances, give J2:J101 a red-fill conditional formatting rule:

=$J2<0

Step 5: Carryover at year end

On the last day of the year, type 2026-12-31 over the =TODAY() in Settings!B2, then work out what each person brings forward, up to the cap and never below zero:

=MIN(MAX(0,J2),E2)

Save a copy for the old year. In the new copy, paste those results into "Unused last year" with Paste Special > Values (Ctrl+Alt+V, then V) so they stop recalculating. Then update B1, put =TODAY() back in B2, and clear the old requests.

Step 6: A who's-out calendar with conditional formatting

On a Calendar sheet, type any date in the month you want to see in A1. In B2, get the first of that month:

=DATE(YEAR($A$1),MONTH($A$1),1)

In C2, add one day until the month ends, then copy C2 across to AF2 (31 columns). EOMONTH($B$2,0) is the last day of the month, so short months end in blank cells:

=IF(B2="","",IF(B2+1>EOMONTH($B$2,0),"",B2+1))

To show just the day number, select row 2, press Ctrl+1 and use the custom number format d. Put employee names in A3 and down. In B3, show X for approved time off and P for pending, then copy across and down:

=IF(OR($A3="",B$2=""),"",IF(COUNTIFS(Requests!$A:$A,$A3,Requests!$C:$C,"<="&B$2,Requests!$D:$D,">="&B$2,Requests!$H:$H,"Approved")>0,"X",IF(COUNTIFS(Requests!$A:$A,$A3,Requests!$C:$C,"<="&B$2,Requests!$D:$D,">="&B$2,Requests!$H:$H,"Pending")>0,"P","")))

In the row under the last name, count who's out each day (check it before approving a request):

=IF(B$2="","",COUNTIFS(Requests!$C:$C,"<="&B$2,Requests!$D:$D,">="&B$2,Requests!$H:$H,"Approved"))

Select the grid starting at B3, open Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and add one rule per formula:

Approved, green fill: =B3="X"
Pending, yellow fill: =B3="P"
Weekends, grey fill: =AND(B$2<>"",WEEKDAY(B$2,2)>5)
Company holidays, light blue fill: =COUNTIF(Holidays,B$2)>0

If a cell matches two rules, the higher one in Conditional Formatting > Manage Rules wins, so keep Approved and Pending at the top. Select B3 and use View > Freeze Panes to keep names and dates visible.

Worked example

As-of date October 16, 2026; holidays include July 3 and November 26. Dana (hired 2021) gets 15 days a year, with 8 unused days from 2025 and a cap of 5. Luis was hired June 1, 2026, also at 15 days.

Employee	Type	Dates	Half day	Status	Days
Dana	Vacation	Feb 16–20		Approved	5
Dana	Vacation	Jul 2–6		Approved	2
Dana	Sick	Mar 10–11		Approved	2
Dana	Personal	Oct 23	Yes	Pending	0.5
Luis	Vacation	Sep 8–11	Yes	Approved	3.5
Luis	Vacation	Nov 25–27		Approved	2
Luis	Vacation	Dec 21–23 (override 2)		Denied	2

July 2–6 counts as 2 days because July 3 is a holiday and July 4–5 is a weekend. November 25–27 counts as 2 because of Thanksgiving.

Employee	Carryover	Accrued	Taken	Pending	Available
Dana	5	11.88	7	0.5	9.88 (79.04 hours)
Luis	0	5.67	5.5	0	0.17

Dana's carryover is capped at 5 and she has accrued 15 × 289 ÷ 365 days; her sick days (the sick formula shows 2) and pending half day don't reduce the balance. Luis has accrued 15 × 138 ÷ 365 days. His November trip counts as taken once approved, so he has 0.17 days left; the denied request is ignored. On the November calendar his row shows X on the 25th to 27th, the 26th is shaded as a holiday, and the count row shows 1.

Common Excel mistakes

Dates stored as text. A date typed in a format your regional settings don't recognize becomes text, and SUMIFS silently skips it. Check with =ISNUMBER(C2), which should return TRUE. Ctrl+; enters today's date safely.
Totals inside the request columns. A total in column G adds itself and causes a circular reference. Keep balances on the Employees sheet.
Overwritten formulas. Unlock the input columns (Ctrl+1 > Protection > untick Locked), then use Review > Protect Sheet so formula cells can't be typed over.

Using Google Sheets instead? See How to track PTO in Google Sheets. Still deciding the rules themselves? Start with the PTO policy template.

Get the finished template

The free Lite version of Simple PTO Tracker is an .xlsx file with this already built: Settings (policies with carryover caps, leave types, holidays), an employee roster, a request log with dropdowns and a days override for half days, automatic balances with daily accrual, and a monthly calendar with approved and pending time off. No macros. Lite covers up to 5 employees; the full version ($14) covers up to 100 employees and 2,000 requests.

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.