# Create a Dynamic Calendar with function SEQUENCE and some formatting

Excel Version
1. 365
Let’s create a dynamic calendar in Excel the quick and easy way. We can use the clever new Excel function SEQUENCE in Excel 365 Insider. Mr. Excel (Bill Jelen) sparked this idea with this video and I enhanced the concept so that all we need is a date to make the calendar matarialise in the spilled cells, automatically formatted with Conditional Formatting.

Format a block of 42 cells (7Cx6R), here B5 to H10, using Conditional Formatting (where the format is a Border Outline based on whether each cell contains a number). Format B5 to H10 as custom number format as d. Format A4 as Long Date. Format B4 as a custom number format as mmmm.

Change the date in A4 and the calendar will adjust. Snappy.

Book1
ABCDEFGH
3SunMonTueWedThuFriSat
4Thursday, January 30, 2020 January
5 1234
6567891011
712131415161718
819202122232425
9262728293031
Sheet1
Cell Formulas
RangeFormula
A4A4=TODAY()
B4B4=A4
B5:H10B5=IF(MONTH(SEQUENCE(6,7,EOMONTH(A4,-1)+1-MOD(WEEKDAY(EOMONTH(A4,-1)),7),1))<>MONTH(A4),"",SEQUENCE(6,7,EOMONTH(A4,-1)+1-MOD(WEEKDAY(EOMONTH(A4,-1)),7),1))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:H10Expression=ISNUMBER(B5)textNO

March 18, 2020: MS Excel team has given us a new function [LET] that allows variables to be declared and used internally. It is very powerful and will change the whole game. This is how it works in this application: B5 =LET(grid,SEQUENCE(6,7,EOMONTH(A4,-1)+1-MOD(WEEKDAY(EOMONTH(A4,-1)),7),1),IF(MONTH(grid)<>MONTH(A4),"",grid))

November 12, 2020: Leila posted a super video about how to make a whole-year calendar that uses dynamic array functions.
Author
DRSteele
Views
1,603
First release
Last update
Rating
0 ratings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back