Modern Excel lets you make a whole monthly calendar from just one cell formula. We’ll explain how it’s done with a sample workbook for our supporters.
At its simplest, Excel can make a single month like this from a formula in cell A6 which fills all the cells with the correct dates for that month.
That’s just the start. Change the formula a little to make a calendar with Monday as the start of the week or many months at once.
Extend the sheet to make 12 month calendar starting from any month.
Note: this Excel workbook works with Excel 365 (Windows or Mac), Excel 2024, Excel 2021, Excel for iOS or Android plus Excel online. Older Excel don’t have the Sequence() function.
Make Months with Sequence()
Sequence() is one of the new-ish dynamic array functions and with a little trickery you can use it to automatically make a month calendar.
The first two parameters setup the number of rows and columns – for a calendar that’s 6 rows and 7 columns. Easy. =Sequence(6,7,….
The third parameter is the number to start the sequence. In this case, the number for the top-left cell of the calendar day so that day 1 of the month is in the correct cell of the top row. To do that we combine Weekday() and Choose()
Weekday(<first day of the month>), returns a number for the day of the week. Sunday = 0, Monday = 1 and so on.
Choose() lets you select a value from the source number so Choose( Weekday(<first day of the month>), …. ) gives you a starting value for the top-left cell.
Like this when Monday is the first day of the week (left most column). Month_Year is a named range for the first day of the month.
CHOOSE(WEEKDAY(Month_Year),-5,1,0,-1,-2,-3,-4)
Or Sunday as the start of the week
CHOOSE(WEEKDAY(Month_Year),1,0,-1,-2,-3,-4,-5)
Add that to the Sequence() and you get this.
SEQUENCE(6,7,CHOOSE(WEEKDAY(Month_Year),-5,1,0,-1,-2,-3,-4))
Which fills all the cells from the top-left of the calendar like this
Obviously, more work is necessary. The negative numbers, zero and days beyond the month have gotta go.
There are several ways to do that. You can get a similar effect with conditional formatting but the Text() option is more direct and is copied along with the formula.
A Text() function hides the cells that don’t apply, in other words days before or after the month. EOMonth() returns serial value for the last day in a month.
TEXT(SEQUENCE(6,7,CHOOSE(WEEKDAY(Month_Year),-5,1,0,-1,-2,-3,-4)),” [>”&DAY(EOMONTH(Month_Year,0))&”];;#”)
Linking Months
Additional month calendars are linked to the first month. The top-left cell in the month title has that month’s date. Changing the month (second) value of the Date() function sets the month. For example: =DATE(Year,Month+1,1) displays the next month from the base date.
Month Calendar with Sequence() – download
We’ve just added another calendar to the ones already in the Office Watch – 2024 Annual, our Thank you for people who help keep Office Watch alive. It’s a late arrival to the bonus download, added in mid-December 2024.
Month Calendar with Sequence().xlsx has three sample calendars to get you started on separate tabs
Sunday start of week
Monday start of week
Monthly calendars with either Sunday or Monday at the first day of the week (left most column).
12 months
Choose a month & year to display calendars for the next 12 months from any month (not just January) See top of this article for an example.
We went to town with extras on the 12 months tab to make formatting easier and consistent.
Day of Week labels
The day of week labels are copied from the first month into the other months. Each of the seven days is a named range e.g. Label_Mon, Label_Tue etc.
If you prefer other labels (maybe another language?) just change the labels in the top-left month.
Cell Styles
All the formatting is done with cell styles. Change the style and all months will update
Month Title
DOW_title
Weekday_number
Weekend_number
