Team Holiday Calendar 2027: Free Excel Template (US, UK)
On this page
Every December someone opens last yearâs leave spreadsheet, copies the tab, fixes the dates, and forgets that Christmas Day 2027 is a Saturday. In January two people book the same week, and in July the team finds out the hard way that the Scottish developer was off on a bank holiday the English ones work.
This template does the boring part for you. It is free, it is a normal Excel file, and it already knows 2027.
Short answer: download the team holiday calendar 2027 (Excel, 47 KB). One row per person, one column per day of 2027. Pick each personâs region (US, England and Wales, Scotland, Northern Ireland) and their public holidays shade themselves, observed and substitute days included. Type V, H, S or O for leave; the sheet counts days taken and left, and the âpeople outâ row turns red when too many people are away on the same day. No macros, no sign-up, and it opens in Google Sheets too.
What the template does
| Part | What it does |
|---|---|
| Planner 2027 | 30 rows for people, 365 columns for days, months and weekdays in the header, frozen names |
| Region | a drop-down per person: US, UK-EW, UK-SC, UK-NI; that personâs public holidays turn amber |
| Leave codes | V vacation day, H half day, S sick, O other; a drop-down blocks typos |
| Allowance, Taken, Left | Taken counts V as 1 and H as 0.5; S and O are not taken from the allowance; Left turns red below zero |
| People out | counts V, H, S and O for each day; turns red at the number in cell E3 (3 by default) |
| Weekends | grey, from the real date, so they stay right if you copy the sheet to another year |
| Holidays sheet | 39 dated rows for the four regions, with sources; add your company days off with Region = ALL |
Everything is plain formulas and conditional formatting: COUNTIF for the totals and the daily
count, COUNTIFS against the Holidays sheet for the regional shading, WEEKDAY for weekends. We
recalculated the example rows before publishing: Alexâs four vacation days and one half day give
4.5 taken and 15.5 left, 5 July 2027 shades for the US row and not the Scottish one, and
30 November shades for Scotland only.
Fill it in, in five minutes
- Names and regions. One person per row. Pick the region where they work, not where the company is registered; a remote developer in Glasgow gets St Andrewâs Day.
- Allowance. Their yearly leave in days (see the next section for what to enter).
- Leave. Type V in the cells of each vacation day, H for a half day, S for sick and O for anything else (parental, training, jury service). Leave weekends and amber holidays empty: they are not working days anyway.
- The alert. Put your threshold in cell E3: the number of people out at which a day becomes a problem.
- Share it. Keep the file in a shared drive, or import it into Google Sheets, so there is one copy, not one per manager.
To add a company-wide day off, such as the day after Thanksgiving or a shutdown between Christmas and New Year, add a row on the Holidays sheet with the date and Region = ALL. It shades in every row.
The 2027 holidays already in it
From the official lists, with weekend dates already moved to the day that is off:
| Region | Days off in 2027 | Worth knowing |
|---|---|---|
| US (federal) | 12 | Juneteenth observed Fri 18 June, Independence Day Mon 5 July, Christmas Fri 24 Dec, and New Yearâs Day 2028 on Fri 31 Dec |
| UK: England and Wales | 8 | Christmas and Boxing Day substitutes on Mon 27 and Tue 28 Dec |
| UK: Scotland | 9 | 2nd January substitute Mon 4 Jan, Summer bank holiday Mon 2 Aug, St Andrewâs Day Tue 30 Nov |
| UK: Northern Ireland | 10 | St Patrickâs Day Wed 17 Mar, Battle of the Boyne Mon 12 July |
US dates come from the Office of Personnel Management, UK dates from GOV.UK. The US list has 12
rows, not the 11 on OPMâs 2027 table, because 1 January 2028 is a Saturday and OPMâs rule makes
âthe preceding Fridayâ the holiday: 31 December 2027 is a day off in 2027. Federal holidays are the
days federal offices close; private employers choose their own list, so delete the rows your company
works. The full tables, and .ics files for calendars and Jira Service Management, are in our
Jira holidays guide.
What to enter as the allowance
UK. GOV.UK: âMost workers who work a 5-day week must receive at least 28 daysâ paid annual leave a yearâ, âthe equivalent of 5.6 weeksâ. And: âAn employer can choose to include bank holidays as part of a workerâs statutory annual leave.â The template shows bank holidays separately, so if your contracts include them in the 28, enter 20 for England and Wales (28 minus 8 bank holidays), 19 for Scotland and 18 for Northern Ireland. If bank holidays come on top, enter 28 or whatever the contract says. Part-time staff get the same 5.6 weeks pro rata. GOV.UK, holiday entitlement.
US. There is no federal minimum. The Department of Labor says the Fair Labor Standards Act âdoes not require payment for time not worked, such as vacations, sick leave or federal or other holidaysâ. Most employers still give paid vacation; the Bureau of Labor Statistics measured private-industry averages of 11 days after 1 year of service, 16 after 5, 18 after 10 and 20 after 20 in March 2026 (BLS chart). Our PTO benchmarks by industry has the breakdown, and the PTO accrual calculator works out what someone has earned so far if leave accrues per pay period.
How many people can be out at once?
No law or standard sets this; it is your minimum staffing. Work it out from what a day needs:
- Support or on-call teams: the people the rota needs, plus one for sickness. A support team of 6 that needs 4 on shift should alert at 3 people out (6 â 4 + 1).
- Product teams: the skills a day needs. If only two people can approve a release, they should not be out together, whatever the head count says. Put them next to each other in the sheet so the clash is easy to see.
- Small teams: in a team of 4, two people out is half the team. Alert at 2.
The red cell is a warning, not a veto. It is there so the second and third requests for the same week are a conversation in January, not a surprise in July.
Excel, Google Sheets, a shared calendar or an app?
| Option | Good at | Weak at |
|---|---|---|
| This Excel template | the year at a glance, regional holidays, totals, free | one person edits at a time; no requests or approvals; no history |
| The same file in Google Sheets | several editors at once, access by link | anyone with edit access can change anyoneâs row |
| A shared Outlook or Google calendar | everyone already has it open, reminders | no totals, no balance, holidays per person are manual |
| A leave app | requests, approvals, balances, an audit trail | costs money, one more tool to roll out |
A spreadsheet is the right tool for a team of up to 20 or so that trusts each other and has one person who owns the file. It starts to hurt when leave needs approval, when people argue about balances, or when the team plans its work in a tool the spreadsheet cannot see.
When the team works in Jira
If your team plans sprints in Jira, the holiday calendar and the sprint plan drift apart: Jiraâs boards do not read a spreadsheet, and as our Jira holidays guide shows, Jira itself has no site-wide holiday calendar. OutSync, our Jira and Confluence app, keeps leave where the work is: people request leave in Jira, managers approve it there, balances update, the board shows who is out today and in the coming weeks, and approved leave goes to a shared Google Calendar. Its Advanced edition adds public-holiday calendars by region, the same idea as the Region column in this template. It is free for up to 10 users.
Until then, the spreadsheet works. Download it, fill in the names, and let the red cells start the conversations early.
Frequently asked questions
Is there a free team holiday calendar template for 2027?
Yes. Ours is a free Excel file with no sign-up and no macros: a row for each of up to 30 people, a column for every day of 2027, the US federal holidays and the UK bank holidays for England and Wales, Scotland and Northern Ireland, leave codes, totals and a "people out" count that turns red at the number you choose.
How do I make a team holiday calendar in Excel?
Put people in rows and the 365 days of the year in columns, with real dates in a header row. Use a code per kind of leave (V, H, S), count them per person with COUNTIF, count each day's absences with COUNTIF down the column, and use conditional formatting for weekends (WEEKDAY) and public holidays (COUNTIFS against a holiday list with a region column). Our template is built exactly that way, so you can copy the formulas.
Does the template work in Google Sheets?
Yes. Import the .xlsx in Google Sheets (File > Import). The formulas, the drop-down lists and the conditional formatting, including the region-specific holidays, use only functions Google Sheets supports: COUNTIF, COUNTIFS, IF, OR and WEEKDAY.
Do bank holidays count towards the 28 days of UK holiday?
They can. GOV.UK says most workers on a 5-day week "must receive at least 28 days' paid annual leave a year", and "An employer can choose to include bank holidays as part of a worker's statutory annual leave." If your contract includes the 8 England and Wales bank holidays in the 28, enter 20 as the allowance in the template, because it already shows bank holidays separately.
How many vacation days do US employees get?
There is no federal minimum: the Department of Labor says the Fair Labor Standards Act "does not require payment for time not worked, such as vacations, sick leave or federal or other holidays". In practice, the Bureau of Labor Statistics measured an average of 11 paid vacation days after 1 year in private industry, 16 after 5 years, 18 after 10 and 20 after 20 (March 2026).
How many people can be on vacation at the same time?
There is no legal or standard number; it is your minimum staffing. Count the people a day needs (on-call, support cover, someone who can approve releases) and set the template's alert cell to team size minus that number plus one. The "people out" row then turns red on every day that would leave you short.