Back to blog
Jobs, stock and customers

PTO tracker: a spreadsheet that does the math, and when to switch

A PTO tracker for 8 to 40 people: sheets to paste, accrual, carryover and sick-time formulas, a who's-out view, and when payroll software pays off.

Start from the approvals template

Purchases and time off, approved from your phone.

Use the template, free
On this page

A PTO tracker needs two sheets. A log gets one line for every approved day off, and a balance sheet has one line per person where formulas work out hours earned, hours taken and hours left. Nobody types a balance. You type each new absence, and the sheet does the rest.

It’s for the owner or office manager who tracks paid time off for 8 to 40 people. You get the columns, a block to paste into a blank sheet, the formulas for three accrual methods, a separate one for sick time earned by hours worked, a filled-in example for four people, a who’s-out view, and a plain look at when payroll or HR software is worth paying for. If your balances already live in a spreadsheet, spreadsheet to app turns the workbook into an app, no account needed to try it.

How we researched this: in November 2026, from the California Labor Commissioner, Washington L&I, the Texas Workforce Commission and the US Department of Labor, and from each vendor’s own pages. Prices are list prices as those pages show them.

What goes in a PTO tracker spreadsheet?

Two sheets, because two different things happen. People take time off all year, and balances change as people earn it. The unit is hours, so a half day works. The examples are made up.

Sheet 1, “Time off”: the log, one line per absence.

ColumnWhat goes in it
A NameThe person, picked from a list
B First dayA real date
C Last dayA real date
D HoursWorkdays × 8, or typed for a part day
E StatusApproved, Pending or Declined
F TypeVacation, sick, personal or unpaid

Sheet 2, “Balances”: one line per person. Every number is a formula except the policy columns.

ColumnWhat goes in it
A NameThe person
B MethodPer pay period, per hour worked, or front-loaded
C Yearly hoursWhat the policy gives, such as 120
D Paydays so farPaydays this year (per pay period)
E Hours workedFrom payroll (per hour worked)
F Carried inFormula: last year’s balance, cut to the carryover cap
G EarnedFormula, by method
H TakenFormula: approved hours from the log
I BalanceFormula: carried in + earned − taken, held at the cap
J CapThe most they can hold, such as 160
K StatusFormula: OK, Use soon or Over
L Ended last yearLast year’s closing balance, typed once in January

Three settings sit to the right: N2 is the carryover cap in hours (40), O2 is paydays a year (26) and P2 is hours in a work year (2,080). Here are four people after 22 of 26 biweekly paydays. Maria and Dev earn by payday. Tasha works part time, so she earns by the hour. Rosa’s yearly amount arrives all at once on January 1.

Those are the numbers the formulas below produce.

Paste it in one go

Make a sheet named Time off, click A1 and paste this: the six headings and one example absence with its Hours formula. Then make a sheet named Balances, click A1 and paste the second block: the headings, the three settings and Maria’s row with every formula. Fill G to K down for each person, and format the dates as dates.

Name	First day	Last day	Hours	Status	Type
Maria Lopez	2/23/2026	2/27/2026	=NETWORKDAYS(B2,C2)*8	Approved	Vacation
Name	Method	Yearly hours	Paydays so far	Hours worked	Carried in	Earned	Taken	Balance	Cap	Status	Ended last year		Carryover cap (hours)	Paydays a year	Hours in a work year
Maria Lopez	Per pay period	120	22		=MIN(L2,$N$2)	=IF(B2="Per pay period",C2/$O$2*D2,IF(B2="Per hour worked",C2/$P$2*E2,C2))	=SUMIFS('Time off'!$D$2:$D$500,'Time off'!$A$2:$A$500,A2,'Time off'!$E$2:$E$500,"Approved")	=MIN(F2+G2-H2,J2)	120	=IF(I2<0,"Over",IF(I2>=0.8*J2,"Use soon","OK"))	16		40	26	2080

How do you calculate PTO accrual in a spreadsheet?

Pick one of three methods per person. The Earned formula in G2 checks column B and does the right math:

  1. Per pay period. Yearly hours ÷ paydays a year × paydays so far. 120 hours on a biweekly payroll is 120 ÷ 26 = 4.62 hours each payday. After 22 paydays, Maria has earned 101.5 hours. Paid twice a month? Change O2 to 24. Weekly is 52, monthly 12. Then keep column D to the paydays so far.
  2. Per hour worked. Yearly hours ÷ 2,080 × hours worked. 2,080 is 40 hours × 52 weeks. Tasha has 96 hours a year, so after 1,150 hours worked she has earned 53.1.
  3. Front-loaded. The whole yearly amount on day one. Rosa gets 120 hours in January, and Earned is just that number.
G2: =IF(B2="Per pay period",C2/$O$2*D2,IF(B2="Per hour worked",C2/$P$2*E2,C2))

Taken adds up the approved hours from the log in H2. SUMIFS adds the numbers that meet your conditions: this person, status Approved. Pending requests don’t count until you change their status:

H2: =SUMIFS('Time off'!$D$2:$D$500,'Time off'!$A$2:$A$500,A2,'Time off'!$E$2:$E$500,"Approved")

The balance in I2 is what you carried in, plus what you earned, minus what you took, but never more than the cap:

I2: =MIN(F2+G2-H2,J2)

A status formula in K2 flags the people to talk to. “Use soon” at 80% of the cap is a number you can change:

K2: =IF(I2<0,"Over",IF(I2>=0.8*J2,"Use soon","OK"))

Rosa’s 130 hours is above 80% of her 160-hour cap, so she shows “Use soon”. Dev has 19.7 hours left and a 40-hour Thanksgiving week pending. Approving it would take him to −20.3, so you’d talk to him first.

In the log, D2 counts workdays: =NETWORKDAYS(B2,C2)*8. It skips Saturdays and Sundays, but not holidays. List your paid holidays on a sheet called Holidays and add it:

=NETWORKDAYS(B2,C2,Holidays!$A$2:$A$20)*8

If your people work weekends, as in a restaurant, use this instead, which counts all seven days:

=NETWORKDAYS.INTL(B2,C2,"0000000")*8

Replace the 8 with the person’s usual shift, or type the hours for a 5-hour shift or a 10-hour double. The formulas work in Google Sheets and Excel.

How do you track sick time that accrues by hours worked?

Many states require paid sick leave that is earned per hour worked, not by payday, with its own rate and limits. That’s the case for a restaurant with part-time servers, where nobody has a flat yearly number. Two examples from state pages:

StateThe rule
WashingtonOne hour of paid sick leave for every 40 hours worked, with no cap on how much builds up in a year. At least 40 hours of unused balance carries over (L&I).
ColoradoOne hour for every 30 hours worked, up to 48 hours a year (Department of Labor and Employment).

Other states and cities have their own rates, so look up yours. Then add a Sick sheet. A is the person, B is hours worked, C earned, D used and E the balance, with the rate in G2 (30) and the yearly cap in H2 (48):

C2: =MIN(B2/$G$2,$H$2)
D2: =SUMIFS('Time off'!$D$2:$D$500,'Time off'!$A$2:$A$500,A2,'Time off'!$E$2:$E$500,"Approved",'Time off'!$F$2:$F$500,"Sick")
E2: =C2-D2

For a state with no cap, like Washington, change C2 to =B2/$G$2 and G2 to 40. In the example, Dev has worked 1,640 hours. That’s 54.7 earned, cut to the 48-hour cap, and with 8 hours used his sick balance is 40.0. Tasha, at 1,150 hours, has earned 38.3 and used none. Paste this on a Sick sheet:

Name	Hours worked	Earned	Used	Balance		Hours worked per sick hour	Yearly cap
Dev Patel	1640	=MIN(B2/$G$2,$H$2)	=SUMIFS('Time off'!$D$2:$D$500,'Time off'!$A$2:$A$500,A2,'Time off'!$E$2:$E$500,"Approved",'Time off'!$F$2:$F$500,"Sick")	=C2-D2		30	48

If sick time is its own bucket, don’t also count it as PTO. In the Balances sheet, add the Type condition to Taken, ending ...,'Time off'!$F$2:$F$500,"<>Sick"). In the example above, PTO is one pool and Dev’s sick hours come out of it.

For a restaurant, you’ll type hours worked from payroll every payday. That’s the point where a payroll tool starts to pay for itself.

To watch hours past 40 a week, see our overtime tracker guide.

What carryover cap and balance cap should you set?

They’re different limits, and people mix them up:

  • Carryover cap: the most that moves into next year. If Rosa ends the year at 60 hours and the cap is 40, she starts January with 40 plus the new year’s 120. In the sheet, F2 is =MIN(L2,$N$2), where L is “Ended last year”.
  • Balance cap (accrual cap): the most anyone can hold at one time. Rosa’s is 160, or about four weeks. At the cap, no more accrues until she takes time off.

At year end, run a short routine: type each closing balance into L, then set paydays and hours worked back to 0, and clear Taken by starting a new log sheet. Mid-year hires start with D at their first payday. Both limits keep a liability from growing. Depending on your state, an unused balance can be money you owe, and a cap stops that number from creeping up unnoticed.

Do you have to pay out unused PTO? What the rules say

It depends on the state, and you need to know yours before you write “use it or lose it” into a handbook. This isn’t legal advice, and these are three examples, not a survey.

WhereWhat it says
FederalThe Department of Labor says the Fair Labor Standards Act doesn’t require pay for time not worked, such as vacations. It’s up to the employer and employee.
CaliforniaThe Labor Commissioner treats earned vacation as wages. Unused vacation is paid at the final rate when employment ends. Use-it-or-lose-it policies aren’t allowed, but a cap on how much can accrue is.
TexasThe Texas Workforce Commission says employers decide whether unused vacation is paid at separation. The Payday Law enforces what your written policy promises, so write it down.

In California, don’t build a “lose it” column. Use a balance cap instead. In Texas, the policy you write decides, so write it clearly.

How do you show who’s out this week?

Add a “Who’s out” sheet that reads the log. Put the Monday of the week in B1 and the Sunday in B2, then ask for every approved absence that overlaps the week:

B1: =TODAY()-WEEKDAY(TODAY(),2)+1
B2: =B1+6
B3: =IFERROR(TEXTJOIN(", ",TRUE,FILTER('Time off'!$A$2:$A$500,'Time off'!$B$2:$B$500<=B2,'Time off'!$C$2:$C$500>=B1,'Time off'!$E$2:$E$500="Approved")),"Nobody")

For the week of August 10, B3 shows “Dev Patel”. For Thanksgiving week it shows “Nobody”, because Dev’s request is still Pending. FILTER and TEXTJOIN work in Google Sheets and in Microsoft 365 and Excel 2021.

To see months at a glance, add a grid with a row per person and a column per day, and color a cell when an approved absence covers that day. Conditional formatting does the coloring. Also color Pending rows light yellow and the status column amber for “Use soon” and red for “Over”.

When does a PTO spreadsheet stop working?

A spreadsheet is the right place to start and stays fine for a long time. It stops being enough for some predictable reasons:

  • Several people approve time off. Two managers edit the sheet, and somebody types over a balance.
  • You fix balances every payday. Hourly accrual leans on hours worked, which come from payroll, and 40 people means 40 numbers to type.
  • Employees keep asking what they have left. A sheet can’t show each person only their own balance.
  • The numbers have to match payroll. The sheet can’t read your payroll register.
  • Requests arrive by text and sticky note. A log you backfill is a log with mistakes.

If two or three of those are true, here are the options.

Which PTO tracking software should you look at?

Prices are from each vendor’s own pages in November 2026. They’re list prices and they change.

ToolWhat it costs
Homebase$0 for one location, up to 10 employees. Paid from $30 per location a month.
BambooHR$250 a month for 25 or fewer. About $10 per employee above that.
GustoPayroll plans. See its pricing page.
This spreadsheet$0
  • Homebase suits hourly teams: scheduling, time clocks and time off. Its free PTO page says the free plan takes time off requests and approvals. Automatic accrual calculations and balance checks on phones are on paid plans. Its pricing page lists Essentials at $30 and Plus at $70 per location a month, with an Annual/Monthly toggle and a 20% saving badge, so check which rate the price shows. The PTO page doesn’t say which paid plan has accruals, so ask Homebase.
  • BambooHR is an HR system, not only a PTO tool. Its pricing page lists Core at $250 a month for companies of 25 or fewer, and $10 per employee a month above that (its FAQ says $10.25). Time off management and a time off calendar come in every plan. A 15-person office pays about $3,000 a year billed monthly, and a 40-person one about $400 a month, before volume discounts.
  • Gusto is payroll first. Its help center describes time off policies where people accrue by hours worked or by a fixed amount, with a maximum balance and carryover limits, and says how unused time is handled when someone leaves. So balances can sit in the same system as payroll. See its pricing page for plans and prices.

Which would we look at first? If you already run payroll, ask that vendor what its time off feature does, because accruals that read hours worked from payroll are what a spreadsheet can’t match. With about 10 hourly people, look at Homebase. For a salaried office of 20 where HR is part of one person’s job, a spreadsheet with a clear policy is still a fair answer.

Can GoodTaco track balances, requests and who’s out?

Yes. Ask GoodTaco for a Balances page and you get one line per person with yearly hours, hours earned, hours taken from approved time off requests and the balance, held at the cap, with who’s out this week at the top. A prompt like this is enough:

Add a Balances page to the time off app. For each person, show yearly hours, hours earned so far, hours taken from approved time off requests, and the balance. Cap the balance at 160 hours. Show who’s out this week at the top.

It sits on the approvals template, a working app for requests and decisions where time off is one of the request types. The sample is a garden center with eight people. A time off request asks for the days, the workdays off and a note, and goes to the person’s manager. In the sample, a request over 5 days also goes to the owner. Approvers say yes, no or “send back with changes” in the app, from a phone if they like, and an “Also out then” box on each request lists who else is off those days. The Team page lists who’s away and who covers.

Already keep your time off log in Excel or a CSV file? Upload it with spreadsheet to app, no account needed to try, and the records go into the app’s own built-in database. Then ask for the Balances page.

To let your team open it, make the app private and add each person in its Users tab, then add them on the Team page with their manager. They sign in with an emailed link or Google, in any browser, including a phone’s. Every version is saved with one-click rollback, and you can print any page from the browser. If your hours come from a timesheet, the timesheet template is the next step, and for the form side of time off, see our guide to a time off request form.

GoodTaco is free to start. Paid plans start at $9 a month, and Pro at $49 a month includes 10 users; bigger teams talk to us about Enterprise.

Know what everyone has left before Friday’s request lands. Start free, no credit card needed.

Frequently asked questions

What is a PTO tracker?

A PTO tracker is a list of how much paid time off each person has earned, how much they have taken and what is left. A spreadsheet version has a balance sheet with one line per person and a log of every day off. The balance is a formula, so nobody types over it.

How do I track PTO in Excel or Google Sheets?

Use two sheets. A Time off log gets one line for every approved absence, with the person, first day, last day and hours. A Balances sheet has one line per person with yearly hours, hours earned, hours taken and the balance. SUMIFS adds up the hours taken from the log, and a MIN formula applies the cap.

How do you calculate PTO accrual?

For per-pay-period accrual, divide the yearly hours by the number of paydays and multiply by the paydays that have passed. A person with 120 hours a year paid every two weeks earns 120 / 26 = 4.62 hours each payday. For per-hour accrual, divide the yearly hours by 2,080 and multiply by the hours worked. For front-loaded PTO, the full amount is available on the first day of the year.

Is there a free PTO tracker?

Yes. You can build one in Google Sheets or Excel for free with the two sheets in this guide. Homebase also has a free plan for one location and up to 10 employees that tracks time off requests, but its page says automatic accrual calculations and balance checks are on paid plans.

Do you have to pay out unused PTO when someone leaves?

It depends on the state. Federal law does not require vacation pay. California treats earned vacation as wages that must be paid out when employment ends. Texas requires payout only if the employer's written policy says so. Check your state's labor agency and write the rule into your policy.

Can an employer have a use-it-or-lose-it PTO policy?

In some states, yes, and in others, no. California's Labor Commissioner says use-it-or-lose-it vacation policies are not allowed, but an employer may cap how much can accrue. Check your state's rules before you write a carryover limit into your policy.

When should I move my PTO tracker out of a spreadsheet?

When several managers approve time off, when you are fixing balances every payday, when people keep asking what they have left, or when the numbers have to match payroll. Payroll tools such as Homebase or Gusto, or an HR system such as BambooHR, keep balances for you. For requests and who is away, an app like GoodTaco's approvals template works.

Free template

Start from the approvals template

Get your own copy of a working approvals app with sample records, ready to make yours. Free to start.

Use the template, free

Or turn your own sheet into an app

Keep exploring

Related guides and templates

View all articles

Guide

Overtime tracker: a free spreadsheet that flags overtime

A free overtime tracker for Excel and Google Sheets: totals each person's week, flags who will pass 40 hours before payday, and works out time-and-a-half pay.

Template

Expense report template with approvals and reimbursements

Turn an expense report template into a real submission app, with manager approvals and reimbursement status your team can trust.

Template

Onboarding checklist template that tracks itself

Turn an onboarding checklist template into a live tracker, with task status, ownership, and progress your whole team can actually see clearly.

Industry

Build PTO Tracking Software From Your Spreadsheet

Build PTO tracking software, an employee onboarding system, and leave tracking from the spreadsheets your HR team already runs. No onboarding. No setup fee.