Free PTO tracker spreadsheet

A PTO tracker for Excel and Google Sheets with working formulas: accrual by pay period, time taken, running balance and a cap warning. One tab for yourself, one for a small team.

Download pto-tracker.xlsx

Excel workbook, about 16 KB. A direct download: no form, no account, no email.

ABCDEFG
3PTO per year (hours)80.00
4Pay periods per year26
6Balance cap (hours, blank = none)
10Accrual per pay period (hours)3.0769
13Pay dateAccrued (hours)Time taken (hours)Lost to cap (hours)Balance (hours)Balance (days)Warning
142026-11-273.080.003.080.38
152026-12-113.080.006.150.77
162026-12-253.080.009.231.15
172027-01-083.088.000.004.310.54
182027-01-223.080.007.380.92
192027-02-053.080.0010.461.31
202027-02-193.080.0013.541.69
Preview of the My PTO tab with its starting settings and 8 hours taken on the fourth pay date. Shaded cells are the ones you fill in. The figures are computed by the same formula module as the calculators.

What is in the PTO tracker?

What does the My PTO tab do?

It tracks one person, pay period by pay period. Enter PTO per year, pay periods per year, a starting balance and the first pay date. Each row adds the accrual, subtracts the hours you type as taken, and shows the balance in hours and days. If you enter a cap, accrual stops while the balance is at the cap, the lost hours are counted, and a warning appears one pay period before the cap is reached.

What does the Team tab do?

It summarizes up to 15 people. List each person once with their PTO per year, pay periods per year, starting balance and cap, and enter how many pay periods have passed this year. Record absences on the Team log tab (date, name, hours); the Team tab adds them up and shows each balance with a cap warning. The Team tab flags a balance that has reached the cap but does not stop accrual there; for exact cap handling keep one My PTO tab per person.

What formulas does the tracker use?

The same ones as the PTO accrual calculator. Accrual per pay period = PTO per year ÷ pay periods per year, so 80 hours a year paid every two weeks is 3.0769 hours a period. Balance = previous balance + accrual credited − time taken. Balance in days = hours ÷ hours in a day.

How do I open the PTO tracker?

In Excel

Open the downloaded file. If Excel shows it in Protected View, choose Enable Editing so you can type in the shaded cells.

In Google Sheets

Upload the file to Google Drive and open it with Google Sheets, or in a new sheet choose File, then Import, then Upload. The tracker uses only formulas that Excel and Google Sheets share.

If you are paid twice a month or monthly

The Pay date column steps forward by a fixed number of days, which fits weekly and every-two-weeks pay. For other schedules, type your real pay dates over the dates in that column; the balance formulas do not depend on the dates.

Questions about the tracker

Is the PTO tracker free?

Yes. The download is a direct link to the file. There is no form, no account and no email required.

Does the PTO tracker work in Google Sheets?

Yes. It uses only formulas that Excel and Google Sheets share. Upload the file to Google Drive and open it with Google Sheets.

How does the tracker calculate the balance?

Accrual per pay period is PTO per year divided by pay periods per year. Each row adds the accrual, subtracts the time taken, and, if a cap is set, credits only the room left under the cap.

The tracker does arithmetic. It does not know your employer's policy or your state's rules. This is general information, not legal advice.

More calculators