Simple PTO Tracker › Guide

How to track PTO in Google Sheets (step by step, with formulas)

Updated October 2026 · About 10 minutes to read

If you run a small team without an HR department, a well-built spreadsheet is often all you need to track paid time off. This guide walks through building one in Google Sheets from scratch: which columns to use, how to calculate accrual (per pay period, monthly or front-loaded), how to cap carryover, and the exact formulas for balances and a team calendar. Every formula below was checked in a test spreadsheet, and they use functions that exist in both Google Sheets and Excel. (Working in Excel? See How to track PTO in Excel.)

You'll build four tabs:

• Settings: year dates, holidays and leave types
• Requests: one row per time-off request
• Employees: one row per person, with their balance
• Calendar (optional): who's off on which day

Formulas here use commas between arguments, which is the default in US English. If your spreadsheet locale uses semicolons (common in Europe), replace each comma with a semicolon.

Step 1: Create a Settings tab

Name the first tab Settings and fill in these cells:

EDATE moves a date forward by a number of months, so EDATE(B1,12)-1 is the last day of your 12-month year. If B3 shows a number like 46387, format it with Format > Number > Date.

Keeping these values in one place means every other formula can point at them, and next year you only change B1.

Step 2: Log every request on a Requests tab

Create a tab named Requests with these headers in row 1:

In F2, count the working days in the request, skipping weekends and your holiday list:

=IF(OR(C2="",D2=""),"",IF(E2<>"",E2,NETWORKDAYS(C2,D2,Settings!$D$2:$D$20)))

NETWORKDAYS counts Monday to Friday between two dates, including both ends, and skips any dates in the holiday range. The IF wrapper leaves the cell blank on empty rows and lets you type a number in column E (such as 0.5 for a half day) to override the count.

In H2, look up whether the leave type counts against PTO:

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

Copy both formulas down as far as you need. Then add dropdowns so names and statuses are always spelled the same way: select column A, go to Data > Data validation > Add rule, choose Dropdown (from a range) and enter Employees!A2:A. Do the same for column B with Settings!F2:F10, and give column G a plain dropdown with Pending, Approved, Denied and Cancelled. (Need a form for employees to fill in first? See the PTO request form template.)

If your business is open on Saturdays, use NETWORKDAYS.INTL instead. Its third argument sets the weekend; 11 means only Sunday is off:

=NETWORKDAYS.INTL(C2,D2,11,Settings!$D$2:$D$20)

Step 3: Set up the Employees tab

Create a tab named Employees. Columns A to E are inputs; F to J are formulas you'll add in the next steps.

Step 4: Calculate accrual

Pick the method that matches your written policy and put its formula in G2.

Option A: front-load the full year

Everyone gets the whole allowance on the first day of the year. People hired mid-year get a prorated share based on how much of the year remains:

=IF(B2>Settings!$B$1,ROUND(C2*(Settings!$B$3-B2+1)/(Settings!$B$3-Settings!$B$1+1),2),C2)

Option B: monthly accrual

One twelfth of the allowance is earned for each full month worked this year. DATEDIF(start, end, "M") counts complete months between two dates:

=IF(Settings!$B$2<MAX(B2,Settings!$B$1),0,ROUND(C2/12*MIN(12,DATEDIF(MAX(B2,Settings!$B$1),Settings!$B$2,"M")),2))

The first IF returns 0 for someone whose start date is still in the future, because DATEDIF returns an error when the start date is after the end date.

Option C: per pay period

For a biweekly payroll (26 paydays a year), each payday earns 1/26 of the allowance. First, in K2, find each person's first payday this year on or after their start date (for long-time staff it's simply the first payday in Settings!B4):

=IF(B2<Settings!$B$4,Settings!$B$4,Settings!$B$4+14*(INT((B2-Settings!$B$4)/14)+1))

Then count the paydays from that date through the as-of date and multiply:

=ROUND(C2/26*MIN(26,MAX(0,INT((Settings!$B$2-K2)/14)+1)),2)

For a weekly payroll, change every 14 to 7 (in both formulas) and 26 to 52. This formula credits a full period on a new hire's first payday; if your policy prorates partial periods, adjust that first period by hand.

Option D: spread evenly by day

The employee earns a share of the allowance for every calendar day employed so far this year. It's the smoothest method and handles new hires automatically:

=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 someone with 15 days a year who was employed all of 2026, here is what each method gives on October 18, 2026:

Tenure-based allowances

If people get more days after a number of years, calculate column C instead of typing it. This gives 20 days after 5 full years and 15 before:

=IFERROR(IF(DATEDIF(B2,Settings!$B$2,"Y")>=5,20,15),15)

And if you need each person's next work anniversary (format the cell as a date):

=IF(B2>Settings!$B$2,EDATE(B2,12),EDATE(B2,12*(DATEDIF(B2,Settings!$B$2,"Y")+1)))

Step 5: Apply the carryover cap

In F2, limit last year's unused days to the cap:

=MIN(D2,E2)

At the end of the year, work out what each person can bring into the new year. Set the as-of date (Settings!B2) to the year end date, then use:

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

MAX(0, ...) stops a negative balance from carrying forward as a negative number. Copy those results and use Edit > Paste special > Values only into column D of next year's copy of the sheet. Pasting values matters: if you paste formulas, they will keep recalculating against the new year's data.

Step 6: Calculate taken, pending and available

In H2, add up approved days that count as PTO and start within this year:

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

SUMIFS adds the Days column only for rows that match every condition. Joining an operator to a cell with ">="&Settings!$B$1 is how you compare against a date stored in another cell.

In I2, the same formula with "Pending" shows requests that haven't been approved yet:

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

Finally, in J2:

=F2+G2-H2

To flag anyone who goes negative, select column J, open Format > Conditional formatting, choose Custom formula is and enter =$J2<0 with a red fill.

Step 7 (optional): Build a monthly calendar

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

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

In C2, add one day as long as it's still the same month, then copy C2 across to AF2 (31 columns):

=IF(B2="","",IF(MONTH(B2+1)=MONTH($B$2),B2+1,""))

Put employee names in A3 and down. In B3, show an X if that person has an approved request covering that date, then copy it 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!$G:$G,"Approved")>0,"X",""))

To shade weekends, select the grid and add a conditional formatting rule with the custom formula =AND(B$2<>"",WEEKDAY(B$2,2)>5).

Common mistakes

• Subtracting dates to count days. =D2-C2 for Monday to Friday gives 4, not 5, and it counts weekends. Use NETWORKDAYS.
• Dates stored as text. A date typed in a format your locale doesn't recognize is stored as text and silently ignored by SUMIFS. Check with =ISNUMBER(C2); it should say TRUE.
• Missing $ signs. Without $, ranges like the holiday list shift when you copy a formula down and start missing holidays.
• Counting every status. Denied and cancelled requests should never reduce a balance. Filter on "Approved" as shown above.
• Not filtering by year. If you keep last year's requests on the same tab, the date conditions in SUMIFS keep them out of this year's totals.
• Requests that cross the year boundary. A request from December 29 to January 2 is counted in the year of its first day. Split it into two rows if you need each year to be exact.
• Typos in names. "Sam Patel" and "Sam Patel " (with a trailing space) are different to a formula. Dropdowns prevent this.
• Mixing hours and days. Pick one unit for the whole sheet. If you track days, enter 0.25 for 2 hours of an 8-hour day.

Not legal advice. PTO, vacation and sick leave rules vary by state and sometimes by city. Some places require paid sick leave, restrict "use it or lose it" policies, or require unused vacation to be paid out when someone leaves. Check the laws that apply to your business, or ask an employment professional, before setting your policy.

Related: How to calculate PTO accrual · How to track PTO in Excel · PTO policy template