A team vacation spreadsheet for engineering managers,that tells you who's in on release day.
Most PTO tracker templates are built for HR. They count balances, days used and days left. An engineering manager asks different questions: who's out during this sprint, is anyone out on the release date, who's on call next week, how many people are actually in on Wednesday, and which holidays apply to the teammate in Toronto.
This is how I'd set up a team vacation spreadsheet that answers those. Start from the free team vacation calendar template (Excel or Google Sheets, same formulas in both), then add a few rows and columns on top. It takes about half an hour, and every formula below is written to work in both apps.
What you'll end up with
The free template's year calendar plus four additions: on-call and release-duty codes that don't count as time off, an In row that turns red below a minimum headcount, a release row that flags a release day when anyone is out, and a Covered by column.
Step 1
Start from the free vacation spreadsheet template
Open the vacation calendar template, pick the year, and either download the .xlsx or copy it to Google Sheets. If you're planning the rest of this year, choose 2026; you can also change the year later in cell B2 of the Calendar tab, and every date and holiday moves with it.
The Calendar tab has one row per person (Name, Team, PTO, Booked, Left, Sick, Next out) and one column per day for the whole year. You type a code into a day: V for vacation, H for a half day, P personal, S sick, C conference, R remote, O other leave. Add a question mark when a plan isn't confirmed yet, like V?, and the cell shows lighter but still counts. Under the names, the Out row counts who's out on each workday and turns red at the number in B3 (3 by default). The Holidays tab has US holidays with an Off? switch, Month prints one month, and Summary shows days out by month.
So half days, tentative plans and the HR side are covered. On-call, releases, coverage per area and holidays outside the US are not, and the steps below add them. They assume the file as downloaded with up to 18 people: names in rows 9 to 38, the Out row in row 39, January 1 in column I and December 31 in column NP. A hidden row 8 marks each day as a workday (1), weekend (2) or holiday (3), and the new formulas use it to skip days nobody works.
Step 2
Add on-call and release-duty codes to the PTO tracker
This is an addition: two new codes, OC for on call and RD for release duty (the person running the release that day). Type them into a person's days like any other code. They aren't time off: the person is working, just with less room for planned work.
The Out row counts every filled cell except remote days, so it would count OC as out. Teach it the new codes: click I39 and replace its formula with =IF(I$8<>1,"",COUNTIF(I9:I38,"?*")-COUNTIF(I9:I38,"R")-COUNTIF(I9:I38,"R~?")-COUNTIF(I9:I38,"OC")-COUNTIF(I9:I38,"RD")). Then type I39:NP39 in the Name Box at the top left and press Ctrl+R to fill it across the year. COUNTIF ignores case, so oc works too.
Give the codes a color. Select I9:NP38, then in Excel choose Home, Conditional Formatting, New Rule, Use a formula, and enter =I9="OC". In Google Sheets it's Format, Conditional formatting, Custom formula is. Repeat for RD. In Sheets, drag the new rules to the top of the list, or the weekend shading wins on weekend on-call days.
Step 3
Add a coverage row: how many people are in each day
Out is the HR number. The planning question is whether enough people are left. Select rows 40 to 42, right-click and insert three rows under the Out row (in Sheets, Insert 3 rows above). Row 40 is for the whole team, row 41 for one area, row 42 for releases in the next step.
In A40 type In, need at least, and in G40 type your minimum, say 5. In I40 enter =IF(I$8<>1,"",COUNTA($A$9:$A$38)-I39) and fill it across to NP40. Then add a conditional formatting rule on I40:NP41 with the formula =AND(ISNUMBER(I40),I40<$G40) and a red fill. Weekends and holidays stay blank, so they never turn red.
Row 41 counts one area. Type the area in A41 exactly as it appears in the Team column, like Backend, and its minimum in G41, like 2. In I41 enter =IF(I$8<>1,"",SUMPRODUCT(($B$9:$B$38=$A41)*((I$9:I$38="")+(I$9:I$38="R")+(I$9:I$38="R?")+(I$9:I$38="OC")+(I$9:I$38="RD")))) and fill it across. It counts people in that area whose day is empty or a working code. Copy the row for each area you care about.
With the template's example team in 2026, Thanksgiving week shows why this matters. Emma and Ava are out Monday to Wednesday, Mia has a tentative V? on the same days, and Olivia and Noah take Wednesday, November 25. The In row reads 5, 5 and 3, so Wednesday turns red with a minimum of 5. Backend stays at 3 all week. Tentative days count on purpose: an unconfirmed plan is still a risk.
Step 4
Put release dates and sprints on the same sheet
Type Release in A42 and put the release name in the cell for its day, like v4.2 on Tuesday, November 24. The text spills over the empty cells next to it. For a code freeze, type freeze in each of its days in the same row. Add a conditional formatting rule on I42:NP42 with =AND(I42<>"",N(I39)>0) and an orange fill. N() turns blank weekend cells into 0, so only workdays with someone out light up.
In the example file, v4.2 on November 24 turns orange: three people are out. Look straight down that column to see who, since the names stay frozen on the left. Monday, November 30 has nobody out. The other checks before you commit to a date are in the release date availability checklist.
For a sprint total you don't need to count columns. Row 6 holds real dates, so =SUMIFS($I$39:$NP$39,$I$6:$NP$6,">="&DATE(2026,11,16),$I$6:$NP$6,"<="&DATE(2026,11,27)) in any empty cell gives person-days out for the sprint from Monday, November 16 to Friday, November 27. The example team gets 11. Thanksgiving and the day after are holidays, so the sprint has 8 workdays: 8 people times 8 days is 64 person-days, and 53 are left. Turning that into a commitment is covered in planning a sprint when people are out.
Step 5
Add a Covered by column and holidays outside the US
Column H is a thin empty spacer between Next out and January 1. Widen it, type Covered by in H7, and for each person write who covers for them: Sophia Diaz next to Ryan Brooks, for example. When Ryan's row shows a week of V, check Sophia's row for the same days. Keep the Team column to the area names your coverage rows count, like Backend, Frontend and QA.
The Holidays tab is US only, and it applies to everyone. For a teammate in Toronto, type O (other leave: counts as out, uses no PTO) in their row on their own holidays, like Canada Day on July 1 and Canadian Thanksgiving on Monday, October 12, and add a cell note with the name. The team holiday calendar lists the dates by country. The reverse doesn't work: US Thanksgiving is shaded as a day off for the whole sheet, including the Toronto teammate who works it.
Half days (H) count as a whole day out in the Out row, which is the safe reading for planning. Note morning or afternoon in a cell note when it matters.
Step 6
Keep it current with one owner and a two-minute check
One person owns the sheet: usually you, with your tech lead as backup while you're away. The owner doesn't type every date, but fixes mistakes and keeps the on-call rotation filled in.
Ask people to add their dates the day they book, not the week before they leave. An unconfirmed plan goes in as V?, and the question mark comes off once it's booked. Pin the link in your team channel.
Then check it every Monday for two minutes. Scroll the next four weeks for red In cells, orange release cells and any V? in the next two weeks that should be confirmed by now. Ask about those in standup.
Four habits that keep the sheet honest
One file, one link
Never send copies. The day "PTO 2026 FINAL v2" appears in someone's Drive, nobody knows which one is right.
Type it when you hear it
Someone mentions a wedding in June during a 1:1? Put V? on those days before the meeting ends.
Fill on-call a month ahead
Type the rotation for the next four weeks in one go, so the In rows and the release row already know about it.
Start next year in December
Make a copy, type the new year in B2, keep the names and clear the codes, as the template page describes. Your added rows and formulas come along.
A spreadsheet is a good start.Here's where it stops.
For eight people in one country and a release a month, this sheet does the job (still choosing between a sheet and a shared calendar? This comparison helps). These are the three places it tends to break for an engineering manager.
It's only as true as the last edit
When only you edit it, every date waits for you. When everyone edits it, someone sorts the names, pastes over a formula row, or keeps a private copy. Either way the sheet goes wrong without looking wrong.
Past four weeks it's a wall of cells
One narrow cell per person per day reads fine for a fortnight. Zoom out to a quarter of releases and you're squinting at letters, not seeing who's away when.
It never tells you anything
The red and orange cells only help if someone opens the sheet before the release date is set in planning. Nothing warns you when a date moves or a vacation lands on it.
Keep the template for the year. Plan the team on a timeline.
Forgot puts each person on their own row with vacation, on-call, release duty, half days and tentative plans as labeled bars, and warns you when someone is away on a release date. Your team opens a read-only link with no account.
- A warning when someone is away on a release milestone
- On-call, release duty, half days and tentative bars
- Public holidays for each person's own country
- A weekly who's-out email for each board
Sign in with Google, no card. After the trial it's a one-time payment, not a subscription.
Questions
How do I make a team vacation spreadsheet in Excel?
Put one person per row and one day per column, type a short code into each day off, and add a row under the names that counts who's out each day with COUNTIF. Conditional formatting colors the codes and flags busy days. The free template on forgot.dev already has all of that and works the same in Google Sheets.
How do I count how many people are in each day in Google Sheets?
Count the filled cells in a day's column with COUNTIF(I9:I38,"?*"), subtract the codes that mean working, like COUNTIF(I9:I38,"R") for remote, and you have the number out. People in is COUNTA of the name column minus that number. A conditional formatting rule can turn the cell red below your minimum.
How do I track on-call in a PTO tracker template?
Add a code like OC, type it into the on-call person's days, and make sure your out count subtracts it, because many templates count any filled cell as a day away. Give OC its own color so the rotation stands out next to vacations.
Can a vacation spreadsheet warn me when someone is out on a release date?
Only when you open it. A release row with a conditional formatting rule can turn a release day orange when anyone is out, but the sheet won't tell you when a vacation lands on that date later. A team calendar with release milestones shows that warning on the date itself.
How many people can be out at once on an engineering team?
There's no universal number. Set a minimum per area from what has to keep working: someone who can deploy, two people who can review each other's code, and whoever is on call. Two people per area you ship from is a reasonable starting point for a small team.