2027 Calendar in Excel: Build a Formula-Driven Yearly Calendar with Federal Holidays, Week Numbers and Workdays
Build a 2027 calendar in Excel or Google Sheets that generates itself: monthly grids with SEQUENCE, ISO week numbers, the 2027 U.S. federal holidays with observed dates, a one-page year view and working days per month with NETWORKDAYS.
A 2027 calendar in Excel can be built entirely with formulas: one cell holds the year, twelve monthly grids fill themselves with SEQUENCE, each row shows its week number, weekends and holidays from your list are colored automatically, and a small table counts working days per month. Change the year to 2028 and everything updates — no retyping dates.
This guide is part of Printable Planners & Forms. For day-by-day detail, pair it with the weekly planner template.
2027 at a glance
- January 1, 2027 is a Friday, and so is December 31. It is not a leap year: 365 days.
- Under ISO 8601 (Monday-to-Sunday weeks), January 1–3, 2027 belong to week 53 of 2026; ISO week 1 of 2027 starts Monday, January 4.
- There are 261 weekdays (Monday–Friday) in 2027.
- Easter Sunday is March 28, 2027.
U.S. federal holidays in 2027
| Holiday | Rule | 2027 date (observed) |
|---|---|---|
| New Year’s Day | January 1 | Fri, Jan 1 |
| Birthday of Martin Luther King, Jr. | 3rd Monday of January | Mon, Jan 18 |
| Washington’s Birthday | 3rd Monday of February | Mon, Feb 15 |
| Memorial Day | Last Monday of May | Mon, May 31 |
| Juneteenth National Independence Day | June 19 (Saturday in 2027) | Fri, Jun 18 |
| Independence Day | July 4 (Sunday in 2027) | Mon, Jul 5 |
| Labor Day | 1st Monday of September | Mon, Sep 6 |
| Columbus Day | 2nd Monday of October | Mon, Oct 11 |
| Veterans Day | November 11 | Thu, Nov 11 |
| Thanksgiving Day | 4th Thursday of November | Thu, Nov 25 |
| Christmas Day | December 25 (Saturday in 2027) | Fri, Dec 24 |
The observed dates follow the usual federal rule: a holiday on Saturday is observed the Friday before, one on Sunday the Monday after. That rule also means New Year’s Day 2028, a Saturday, is observed on Friday, December 31, 2027. Private employers set their own holiday schedules, so treat this as the federal calendar and adjust for your company and state.
Step 1: The year and the holiday list
On a “Config” sheet put the year in B1. On a “Holidays” sheet, list dates in column A and names in B. Rule-based holidays can be calculated so the list updates every year — for example, the third Monday of January:
=DATE($B$1,1,1)+MOD(7-WEEKDAY(DATE($B$1,1,1),3),7)+14
The first part finds the first Monday of January; adding 14 days gives the third. Memorial Day (last Monday of May): =DATE($B$1,6,1)-WEEKDAY(DATE($B$1,6,1)-1,3)-1. If you prefer, simply type the dates from an official source once a year.
Step 2: One month, one formula
For January, in A3:
=SEQUENCE(6,7,DATE(Config!$B$1,1,1)-WEEKDAY(DATE(Config!$B$1,1,1),2)+1)
That fills a 6 × 7 grid starting on the Monday of the week containing the 1st. Use the number format d, put M T W T F S S in row 2, and copy the block for the other months, changing the month number. Prefer Sunday-first calendars? Use WEEKDAY(…,1) instead of WEEKDAY(…,2) and S M T W T F S headers.
Conditional formatting
| Rule | Formula (applied to A3:G8) | Format |
|---|---|---|
| Other month | =MONTH(A3)<>1 |
Light gray text |
| Weekend | =WEEKDAY(A3,2)>5 |
Gray fill |
| Holiday | =COUNTIF(Holidays!$A:$A,A3)>0 |
Soft red fill |
| Today | =A3=TODAY() |
Thick border |
Week numbers
In column H: =ISOWEEKNUM(A3). The first row of January 2027 shows 53, the next shows 1. If your organization numbers weeks from January 1 instead, use =WEEKNUM(A3,2).
Step 3: One-page year view
Months in rows (A2:A13 = 1–12), days 1–31 across (B1:AF1). In B2:
=IF(MONTH(DATE(Config!$B$1,$A2,B$1))=$A2,DATE(Config!$B$1,$A2,B$1),"")
The month check prevents a “February 30” (Excel would roll it into March 2). Format as ddd and reuse the same color rules — a compact view for planning team vacations alongside the leave tracker.
Step 4: Working days per month
=NETWORKDAYS(DATE(Config!$B$1,A2,1),EOMONTH(DATE(Config!$B$1,A2,1),0),Holidays!$A:$A)
Using the eleven observed federal holidays above plus the December 31 observance of New Year’s Day 2028, 2027 has 249 federal working days (261 weekdays minus 12 observed holidays). With your own company holidays in the list, the table shows your real number.
Printing
- A month per page: set each block as a print area, landscape, fit to one page.
- Whole year: arrange the blocks 3 × 4 and use “Fit All Columns on One Page”.
- Room to write: make date rows taller (around 45 pixels) and align numbers top-left.
FAQ
My Excel doesn’t have SEQUENCE.
SEQUENCE needs Excel 2021 or Microsoft 365. In older versions, type the first Monday in A3 (=DATE(2027,1,1)-WEEKDAY(DATE(2027,1,1),2)+1) and add 1 in each following cell.
Why does the first week of January show 53?
Because January 1, 2027 is a Friday; under ISO 8601 that week still belongs to 2026.
Does it work in Google Sheets?
Yes. SEQUENCE, WEEKDAY, ISOWEEKNUM and NETWORKDAYS all work the same.
Next step: type 2027 in Config!B1, paste the holiday dates into the Holidays sheet, build January, and copy it for the other eleven months.