Summary
To create a dynamic monthly calendar with a formula, you can use the SEQUENCE function, with help from the WEEKDAY function. In the example shown, the formula in B6 is:
=SEQUENCE(6,7,start-WEEKDAY(start)+1)
where start is the named range J6. Conditional formatting highlights the current date and holidays and lightens days in other months. See below for a full explanation.
Note: dynamic array functions are available in Excel 2021+ and Excel 365. For a formula approach that works in older versions of Excel, see this example.
Explanation
In this example, the goal is to generate a calendar for any month, based on a start date in cell J6 (the named range "start"), which must be a first-of-month date like 1-Oct-2026. The calendar should place each day of the month in a grid, with each week starting on Sunday. The solution is based on the SEQUENCE function, one of the original dynamic array functions in Excel, and a perfect fit for this problem.
Table of contents
- Basic SEQUENCE
- SEQUENCE with dates
- First Sunday
- The WEEKDAY function
- Rolling back to Sunday
- Putting it all together
- Conditional formatting rules
- Calendar Title
- Perpetual calendar with current date
- Monday start
Basic SEQUENCE
The SEQUENCE function generates numeric sequences. For example, to generate the numbers 1 to 10 in ten rows, you can use SEQUENCE like this:
=SEQUENCE(10) // returns {1;2;3;4;5;6;7;8;9;10}
The result is an array of the numbers 1-10, which spills into ten cells. SEQUENCE can also fill rows and columns. This formula creates the numbers 1-10 in 5 rows and 2 columns:
=SEQUENCE(5,2)
And this formula fills a 6 x 7 grid (6 rows and 7 columns) with the numbers 1-42:
=SEQUENCE(6,7)
The worksheet below shows all three formulas:
These are just numbers, not dates, but you can see the core concept.
SEQUENCE with dates
Because Excel dates are serial numbers, SEQUENCE can also generate dates. For example, this formula returns the first 7 days of October 2026:
=SEQUENCE(7,1,DATE(2026,10,1))
Note: the DATE function is a safer way to hard-code a date than text, which can be misinterpreted.
This asks for 7 numbers in 7 rows and 1 column, starting with October 1, 2026. The step defaults to 1, so the result is 7 serial numbers starting with 46296. To show days instead of serial numbers, we can apply the custom number format "d", which displays just the day. The worksheet below shows before and after:
Now let's ask for a 6 x 7 grid starting with October 1, 2026:
=SEQUENCE(6,7,DATE(2026,10,1))
With the number format "d" applied, we see 42 days, beginning with October 1. After October 31, the month changes to November, and the day starts again at 1:
We still don't have a usable calendar, but we're getting closer!
To make a proper calendar, the grid needs to start on a Sunday. If the first of the month isn't a Sunday, the grid should start on the last Sunday of the previous month. How can we calculate that date?
First Sunday
If the first of the month is a Sunday, we're done: the first of the month is our start date. Otherwise, we need to "roll back" to the prior Sunday. How many days? That depends on the day of the week the first lands on. If the first is a Tuesday, we roll back 2 days. If it's a Friday, we roll back 5 days. To do this in a formula, we need the WEEKDAY function.
The WEEKDAY function
The WEEKDAY function returns a number for the day of the week: by default, 1 for Sunday through 7 for Saturday. October 1, 2026 is a Thursday, so WEEKDAY returns 5:
=WEEKDAY(DATE(2026,10,1)) // returns 5
November 1, 2026 is a Sunday, so WEEKDAY returns 1:
=WEEKDAY(DATE(2026,11,1)) // returns 1
Rolling back to Sunday
The table below lines up the number WEEKDAY returns for each day with the number of days to roll back to Sunday:
| First of month | WEEKDAY | Days to roll back |
|---|---|---|
| Sunday | 1 | 0 |
| Monday | 2 | 1 |
| Tuesday | 3 | 2 |
| Wednesday | 4 | 3 |
| Thursday | 5 | 4 |
| Friday | 6 | 5 |
| Saturday | 7 | 6 |
Notice the pattern: the rollback is always one less than the WEEKDAY number. So we can subtract it from the start date like this:
start-(WEEKDAY(start)-1)
Subtracting one less than WEEKDAY is the same as subtracting WEEKDAY and adding 1, so we can drop the parentheses:
start-WEEKDAY(start)+1 // first Sunday
For October 1, 2026 (a Thursday), WEEKDAY returns 5, and the formula rolls back 4 days to Sunday, September 27. When the first is a Sunday, WEEKDAY returns 1, and the date doesn't change. Either way, the result is the first Sunday in the calendar grid.
Putting it all together
Now we can put the pieces together. We ask SEQUENCE for a 6 x 7 array:
=SEQUENCE(6,7 // 6 rows, 7 columns
Then, for the start argument, we provide the expression worked out above:
=SEQUENCE(6,7,start-WEEKDAY(start)+1)
The result is 42 dates, displayed as a monthly calendar. Change the date in J6 to another first-of-month date, and the calendar updates. In the workbook, J6 has a dropdown list of the months in 2026.
Conditional formatting rules
Three conditional formatting rules highlight the current date and holidays and lighten days in other months:
The formula for the current date is:
=B6=TODAY()
The formula to highlight holidays is based on the COUNTIF function:
=COUNTIF(holidays,B6)
Any count other than zero means the date is a holiday. In the example shown, holidays is the named range L6:L8, which contains valid Excel dates. If you add more holidays, update the named range, or define holidays as an Excel Table so the range expands automatically.
The formula to lighten days in other months is based on the MONTH function:
=MONTH(B6)<>MONTH(start)
The rule is triggered when a date's month is different from the month of the start date in J6.
Read more: Conditional formatting with formulas, Excel custom number formats
Calendar Title
The calendar title in cell B4 is created with the TEXT function:
=TEXT(start,"mmmm yyyy")
The title is centered over the calendar grid with Center Across Selection. Select B4:H4, use Control + 1 to open Format Cells, then select "Center Across Selection" from the Horizontal text alignment dropdown. This is better than merging cells because it doesn't alter the grid structure of the worksheet.
Perpetual calendar with current date
To create a perpetual calendar that always shows the current month, calculate the first day of the current month with the EOMONTH function:
=EOMONTH(TODAY(),-1)+1
Enter this formula in J6, and the calendar will always stay up to date. For an all-in-one formula, use the LET function:
=LET(
start,EOMONTH(TODAY(),-1)+1,
SEQUENCE(6,7,start-WEEKDAY(start)+1)
)
Here, LET defines start as the first day of the current month, then runs the original formula unchanged. The local variable start overrides the named range start on the worksheet, which can be deleted if desired.
Monday start
To generate a calendar that starts on Monday instead of Sunday, give WEEKDAY a return_type of 2:
=SEQUENCE(6,7,start-WEEKDAY(start,2)+1)
With return_type set to 2, WEEKDAY returns 1 for Monday and 7 for Sunday, so the same logic rolls the start date back to the Monday on or before the first of the month. Remember to change the day labels above the grid to begin with Monday. To start the week on another day, use return_type 11 (Monday) through 17 (Sunday). For example, start-WEEKDAY(start,16)+1 starts the calendar on Saturday. This example has more information on rolling back dates to previous days of the week.
