PTO carryover at year end in Google Sheets: 2027-ready

Short answer: For each person, unused days = allowance + days carried in − approved days in the year. Carryover is MIN(MAX(0, unused), cap), and anything above the cap is forfeited: MAX(0, unused − cap). A cap of 0 is use-it-or-lose-it. Count requests that cross New Year in both years, warn people who would lose days before December 31, then start 2027 with the new allowance + carryover, pasted as values.

Formulas tested in a real spreadsheet · Google Sheets + Excel · Updated November 2026 · By Hazelshelf

Set up once

Settings tab: B1 the year you're closing (2026), B2 carryover cap in days (5; use 0 for use-it-or-lose-it), B3 next year's allowance (20), B4 optional date carried days must be used by (3/31/2027). Holidays in Holidays!A2:A100. Log tab: A Name, B Type, C Start, D End, E Status. Year End tab: A Name, B this year's allowance, C days carried in from last year. Every number lives in a cell, so next year you change four cells, not twenty formulas.

Step by step

1. Split requests that cross New Year

A request from Dec 28 to Jan 2 belongs to two leave years. Type 2026 in Log F1 and 2027 in G1, put this in F2, then fill it down and one column right. It clips each request to the year in the header and skips weekends and holidays. In our test, Dee's Dec 28–Jan 2 request counted 4 days in 2026 and 0 in 2027 (Jan 1 is a holiday, Jan 2 a Saturday).

=IF(OR($C2>DATE(F$1,12,31),$D2<DATE(F$1,1,1)),0,NETWORKDAYS.INTL(MAX($C2,DATE(F$1,1,1)),MIN($D2,DATE(F$1,12,31)),1,Holidays!$A$2:$A$100))

2. Total each person's days for the year

In Year End D2. Approved includes days already booked for later in December, so the total is right before the year is over. Pending and denied requests don't count.

=SUMIFS(Log!$F:$F,Log!$A:$A,A2,Log!$E:$E,"Approved")

3. Calculate unused days, carryover and what's forfeited

In E2, F2 and G2. MAX(0, …) stops a negative balance from carrying over as a negative. In our test, Ana had 20 + 3 − 9 = 14 unused days: 5 carried over and 9 forfeited. Ben had 4 unused, so all 4 carried over.

Unused (E2):     =B2+C2-D2
Carry over (F2): =MIN(MAX(0,E2),Settings!$B$2)
Forfeited (G2):  =MAX(0,E2-Settings!$B$2)

4. Warn people before December 31

Forfeited is also the number of days someone still needs to book to lose nothing. This lists everyone who would lose days (it returned "Ana, Dee" in our test). Send it to them in November, not on December 30.

=TEXTJOIN(", ",TRUE,FILTER(A2:A50,G2:G50>0,"nobody"))

5. Set the 2027 opening balance

Next year's allowance plus the capped carryover. Ana starts 2027 with 20 + 5 = 25 days.

=Settings!$B$3+F2

6. Optional: make carried days expire

If carried days must be used by a date (say March 31), add Log column H with the first formula, then the second in Year End I2 for what's left to use. It assumes carried days are used first. Ana's Mar 29–Apr 2 trip used 3 days before the deadline, so 2 of her 5 carried days are still to use. For the days that actually expire, use =IF(TODAY()>Settings!$B$4,I2,0).

Log H2:       =IF(OR($C2>Settings!$B$4,$D2<DATE(YEAR(Settings!$B$4),1,1)),0,NETWORKDAYS.INTL(MAX($C2,DATE(YEAR(Settings!$B$4),1,1)),MIN($D2,Settings!$B$4),1,Holidays!$A$2:$A$100))
Year End I2:  =MAX(0,F2-SUMIFS(Log!$H:$H,Log!$A:$A,A2,Log!$E:$E,"Approved"))

7. Roll the file to 2027

Make a copy of the file (File › Make a copy) so 2026 stays as a record. In the copy: select Year End F2:F50, copy it, and paste it into C2:C50 with Edit › Paste special › Values only, so the carryover doesn't change when the old log is edited. Then type 2027 in Settings B1, put the new allowance in column B, change the Log headers to 2027 and 2028, and check the 2027 public holidays are in the Holidays list. Type the year: =YEAR(TODAY()) flips the whole file on January 1, often in the middle of reconciling the old year.

Carryover policies and the setting for each

Policy	Settings B2 (cap)	What happens to unused days
Use-it-or-lose-it	0	All forfeited on Dec 31
Capped carryover	e.g. 5	Up to 5 roll over, the rest are forfeited
Carry over, then expire	e.g. 5, plus a date in B4	Up to 5 roll over, unused carried days lapse after the date
Unlimited carryover	A big number, e.g. 999	Everything rolls over
Accrual cap (no forfeiting)	999, plus a cap on earning	Nothing is lost; earning pauses at the cap (see the accrual ledger)

Is use-it-or-lose-it allowed?

It depends on where your people work. California is the best-known exception: the state Labor Commissioner treats a policy where unused vacation is lost by a set date as illegal, applies the same rule to combined PTO plans, and does allow a cap that stops further accrual until the balance drops below it (California DIR, Labor Standards Enforcement: Vacation FAQ). There, use the accrual-cap row of the table above instead of forfeiting.

Rules differ by state and country, so check the ones that apply to your team. This guide shows how to calculate a policy, not which policy is allowed. It isn't legal advice.

FAQ

How do I calculate PTO carryover in Google Sheets?

Unused = allowance + carried in − approved days this year. Carryover = MIN(MAX(0, unused), cap) and forfeited = MAX(0, unused − cap). With a 20-day allowance, 3 days carried in, 9 taken and a 5-day cap, 5 days carry over and 9 are forfeited.

How do I set up use-it-or-lose-it in a spreadsheet?

Set the carryover cap to 0. The carryover formula then returns 0 for everyone and the forfeited column shows every unused day, which is also how many days each person should book before the year ends.

How do I count a vacation that runs from December into January?

Clip it to each year with NETWORKDAYS.INTL(MAX(start, Jan 1), MIN(end, Dec 31), 1, Holidays), using one column per year. The December days count toward this year's balance and the January days toward next year's.

Should the new leave year start from a formula like YEAR(TODAY())?

For year-end work, no. Type the year in one Settings cell. A TODAY()-based year changes on January 1 by itself, which moves every balance while you're still closing out the old year.

Should carryover be rounded to half days?

Only if your policy says so. =FLOOR(E2,0.5) rounds unused days down to the nearest half day (7.8 becomes 7.5). Otherwise, keep the decimals.

Is unused PTO lost or paid out when someone leaves?

That depends on your policy and local law. In California, earned and unused vacation must be paid out when employment ends (California DIR vacation FAQ). Check the rules where your team works.

More guides: How to track PTO in Google Sheets · PTO accrual formulas · Team leave calendar: avoid clashes