2027 Calendar in Excel or Google Sheets: Formulas for Months, Holidays and Week Numbers

Updated 2026-11-03

Every year someone has to make next year's calendar for the team, the rota or the fridge. Instead of typing 365 dates, you can build a calendar with one formula per month that recalculates when you change the year. This guide builds it step by step, then adds the 2027 holiday dates and week numbers. Examples work in Microsoft 365 or Excel 2021 and in Google Sheets; there is a version for older Excel too.

2027 at a glance

• 1 January 2027 is a Friday, and 2027 is not a leap year, so it has 365 days and February has 28.
• There are 261 weekdays (Monday to Friday) in 2027.
• ISO week 1 of 2027 starts on Monday 4 January. 1-3 January belong to week 53 of 2026.
• After US federal holidays (observed dates) there are 249 working days; after England and Wales bank holidays, 253.

Step 1: Set up the inputs

Put the year in B1 (2027) and a month number in B2 (1 for January). In A3, show the month name as a title:

=TEXT(DATE($B$1,$B$2,1),"mmmm yyyy")

Type the day headings in A4:G4: Sun, Mon, Tue, Wed, Thu, Fri, Sat. (For a Monday start, see Step 3.)

Step 2: One formula for the whole month

In A5, enter:

=LET(first, DATE($B$1,$B$2,1), start, first-WEEKDAY(first)+1, d, SEQUENCE(6,7,start), IF(MONTH(d)=$B$2, d, ""))

It spills into a 6-row by 7-column grid (A5:G10). How it works:

• first is the 1st of the month.
• WEEKDAY(first) returns 1 for Sunday to 7 for Saturday, so first-WEEKDAY(first)+1 is the Sunday on or before the 1st.
• SEQUENCE(6,7,start) lists 42 consecutive dates from that Sunday, filling across each row.
• The IF blanks out dates that belong to the previous or next month.

Select A5:G10 and apply the custom number format d so only the day number shows. Six rows are needed because some months, such as a 31-day month starting on a Saturday, touch six weeks.

In Google Sheets, wrap the formula in ARRAYFORMULA( ) so the IF works on every cell of the grid.

Step 3: Start weeks on Monday

Change the start date so it uses Monday-based weekday numbers, and retype the headings as Mon to Sun:

=LET(first, DATE($B$1,$B$2,1), start, first-WEEKDAY(first,2)+1, d, SEQUENCE(6,7,start), IF(MONTH(d)=$B$2, d, ""))

WEEKDAY(date,2) returns 1 for Monday to 7 for Sunday. February 2027 starts on a Monday, so with this version the 1st sits in the top-left cell.

Step 4: Make all 12 months

Copy the block (title, headings and grid) eleven times, for example in three rows of four months. Give each block its own month cell instead of B2, holding 1 to 12, and point that block's formula at it. Change B1 to 2028 next year and the whole calendar updates.

Older Excel without SEQUENCE or LET

In A5, enter the start date:

=DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1))+1

In B5, enter =A5+1 and fill right to G5. In A6, enter =G5+1, fill B6:G6 with =A6+1 and so on, then copy row 6 down to row 10. To hide other months' days, add conditional formatting with the formula =MONTH(A5)<>$B$2 and set the font colour to white or light grey.

Step 5: Highlight weekends, holidays and today

Select the grid, then Home > Conditional Formatting > New Rule > Use a formula (in Sheets: Format > Conditional formatting > Custom formula). Write each rule for the top-left cell, A5:

• Weekends: =WEEKDAY(A5,2)>5
• Holidays, from a list named Holidays: =AND(A5<>"", COUNTIF(Holidays,A5)>0)
• Today: =A5=TODAY()

The A5<>"" test matters: without it, COUNTIF treats the empty cells of the grid as a match for any empty rows in your holiday list. The weekend and today rules return an error or FALSE for blank cells, so those stay unformatted on their own.

Step 6: US federal holidays 2027 with formulas

Most US holidays are "the nth weekday of a month", which one pattern handles. With the year in B1:

nth weekday:  =DATE(B1,m,1)+MOD(w-WEEKDAY(DATE(B1,m,1)),7)+7*(n-1)
last weekday: =EOMONTH(DATE(B1,m,1),0)-MOD(WEEKDAY(EOMONTH(DATE(B1,m,1),0))-w,7)

Here m is the month, n is 1 for the first, 2 for the second and so on, and w is the weekday number (1 Sunday, 2 Monday, ... 5 Thursday, 7 Saturday).

Fixed-date holidays that land on a weekend are observed on the nearest weekday: Saturday moves to Friday and Sunday to Monday. With the actual date in A2:

=IF(WEEKDAY(A2)=7, A2-1, IF(WEEKDAY(A2)=1, A2+1, A2))

Note the last row: because 1 January 2028 is a Saturday, the New Year holiday is observed on Friday 31 December 2027, so it belongs in your 2027 list.

UK bank holidays 2027 (England and Wales)

Easter moves each year, so it is simplest to type these dates rather than calculate them. Scotland and Northern Ireland have some different bank holidays.

Step 7: Week numbers

=ISOWEEKNUM(A2)
=WEEKNUM(A2)

ISOWEEKNUM follows the international standard used in most of Europe: weeks start on Monday and week 1 contains the first Thursday of the year. WEEKNUM, by default, starts weeks on Sunday and counts the week containing 1 January as week 1. For Friday 1 January 2027, ISOWEEKNUM returns 53 and WEEKNUM returns 1, so pick one and say which in the heading.

Useful checks

• Weekdays in 2027: =NETWORKDAYS(DATE(2027,1,1), DATE(2027,12,31)) returns 261. Add your Holidays list as the third argument to count working days.
• Days in a month: =DAY(EOMONTH(DATE($B$1,$B$2,1),0))
• Leap year test: =DAY(EOMONTH(DATE($B$1,2,1),0))=29

For counting working days and deadlines around these holidays, see NETWORKDAYS and WORKDAY and our guide to days between dates. To print, set Page Layout to fit all columns on one page and use landscape for a four-across layout.

← More guides