Excel calendar template 2027 (and 2026), and how to make your own

In short: put the year in one cell and build each day with =DATE($C$2,month,day). A custom number format such as ddd d shows the weekday, ISOWEEKNUM gives the week number, and conditional formatting shades weekends with WEEKDAY(date,2)>5 and holidays with COUNTIF(Holidays,date)>0. A month grid is one formula with SEQUENCE in Excel 365.

Try it now

Every calendar starts by turning a year into real dates. With the year in one locked cell, a single new number moves the whole calendar to another year.

Task In B2, calculate the first day of the month in column A for the year in E1. The formula is filled down to B7, and the year has to stay in E1.

	A	B	C	D	E
1	Month	First day		Year	2027
2	1
3	2
4	3
5	4
6	5
7	6

Download the 2027 and 2026 Excel calendar

Both files are identical apart from the year preset in C2, so either one works for any year. Each has a yearly calendar with all twelve months side by side, a printable month sheet that starts on Sunday or Monday, and a holiday sheet with US federal holidays and UK bank holidays for England and Wales.

Each year also has its own page with every holiday date, long weekends and week numbers: 2026 calendar, 2027 calendar and 2028 calendar (a leap year). To plan time off for a team, use the PTO tracker.

What is inside the template

Sheet	What it holds	Formula behind it
Year	Year in C2, holidays (US, UK or None) in F2, three columns per month: date, week, notes	=IF(MONTH(DATE($C$2,2,30))=2,DATE($C$2,2,30),"")
Month	Month in C2, week start in F2, six weeks of seven large boxes	=DATE(Y,M,1)-WEEKDAY(DATE(Y,M,1),1)+1
Holidays	Every holiday with its date and region, plus rows for your own days off	Rules such as the third Monday of January, Easter by the Gauss algorithm
Instructions	A short version of this guide

Step 1: create the dates with DATE

Each month gets 31 rows. In the row for February 30, DATE(2027,2,30) quietly returns March 2, because Excel rolls extra days into the next month. So each formula checks whether the result is still in the right month and leaves the cell blank if not.

That one check also takes care of leap years: DATE(2028,2,29) is still in February, DATE(2027,2,29) is not. For more on DATE, MONTH and YEAR, see the lesson on date functions.

Step 2: weekday names and week numbers

You do not need a formula for the weekday, because it is already part of the date. A custom number format displays it: ddd d shows "Fri 1" and dddd the full name. Set it under Home > Number > Custom.

For week numbers, ISOWEEKNUM(date) follows ISO 8601, used in the UK and most of Europe: weeks start on Monday and week 1 contains the first Thursday of the year. WEEKNUM(date) without a second argument starts weeks on Sunday and always puts January 1 in week 1, which is common in the US. The template shows the ISO week next to each Monday with =IF(WEEKDAY(A5,2)=1,ISOWEEKNUM(A5),"").

Step 3: shade the weekends

Grey weekends come from a conditional formatting rule with a formula. WEEKDAY with type 2 numbers Monday as 1 and Sunday as 7, so Saturday and Sunday are everything above 5.

Task Use conditional formatting to highlight the whole rows A2:B9 whose date falls on a weekend. Write the rule formula as it applies to A2.

	A	B
1	Date	Event
2	1/7/2027	Team meeting
3	1/8/2027
4	1/9/2027	Stocktake
5	1/10/2027	Stocktake
6	1/11/2027
7	1/12/2027	Client visit
8	1/13/2027
9	1/14/2027	Training

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

The dollar sign before the A makes the notes column next to the date turn grey too. The template has this rule once per month, because each month has its own date column.

Step 4: mark the holidays

Holidays live on their own sheet, and the rule asks whether each date appears in that list. COUNTIF returns the number of matches.

Task Highlight the rows A2:B9 whose date appears in the holiday list F2:F4. Write the rule formula as it applies to A2.

	A	B	C	D	E	F
1	Date	Note				Holidays
2	12/21/2027	Year-end close				12/25/2027
3	12/22/2027					12/26/2027
4	12/23/2027					1/1/2028
5	12/24/2027
6	12/25/2027	Family
7	12/26/2027
8	12/27/2027
9	12/28/2027	Stocktake

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Most US federal holidays follow a rule rather than a fixed date. The third Monday in January (Martin Luther King Jr. Day) is =DATE(Y,1,1)+MOD(8-WEEKDAY(DATE(Y,1,1),2),7)+14: the first Monday of the month plus two weeks. Memorial Day is the last Monday in May, counted back from May 31. When a fixed holiday such as July 4 falls on a Saturday, federal employees get the Friday off, and a Sunday holiday moves to Monday; the template applies that rule too.

UK bank holidays depend on Easter. The Holidays sheet calculates Easter Sunday with the Gauss algorithm in eight small steps with MOD and INT, and Good Friday and Easter Monday sit two days before and one day after. Weekend bank holidays get their substitute day on the next weekday, just as GOV.UK lists them.

A month grid with SEQUENCE

A printable month is six weeks of seven days. The first box is the Sunday (or Monday) on or before the first of the month: =DATE(Y,M,1)-WEEKDAY(DATE(Y,M,1))+1 for a Sunday start, or with WEEKDAY(…,2) for Monday. Every other box is the previous one plus 1.

In Excel for Microsoft 365, Excel 2021 or later and Excel for the web you can fill the whole grid with one formula: =SEQUENCE(6,7,start) returns six rows of seven consecutive dates as a dynamic array. The template uses the plus 1 version so it also works in Excel 2016 and 2019. Days from the previous or next month turn light grey with the rule =MONTH(B5)<>$C$2.

Yearly planner or printable calendar

The Year sheet doubles as a yearly planner: the wide notes column next to each month is for appointments, school terms or project dates, and the holiday names are already filled in. Color a range with a fill color to block out a vacation. If you need to count working days between two dates, see DATEDIF and NETWORKDAYS. Both sheets are set to landscape for printing.

Common mistakes

Typed dates instead of formulas

If you type the first date and drag down, the calendar is stuck in that year. Dates built from DATE and a year cell follow every change.

A year reference without dollar signs

Without $C$2, the reference to the year slides away when you fill. The lesson on absolute references shows how locking works.

Week numbers that do not match

WEEKNUM and ISOWEEKNUM give different results around New Year. Pick the system your company or country uses and stick to it.

Calendar formulas in German Excel

English	German
DATE, MONTH, YEAR	DATUM, MONAT, JAHR
WEEKDAY, ISOWEEKNUM, WEEKNUM	WOCHENTAG, ISOKALENDERWOCHE, KALENDERWOCHE
COUNTIF, SEQUENCE	ZÄHLENWENN, SEQUENZ

German Excel separates arguments with a semicolon: =DATUM($C$2;1;1).

Which Excel versions support this?

DATE, WEEKDAY, COUNTIF and conditional formatting work in every current version. ISOWEEKNUM is available from Excel 2013. SEQUENCE needs Excel for Microsoft 365, Excel 2021 or later, or Excel for the web; the template does not depend on it.

FAQ

More files are on the Excel templates page.