- Excel Version
- 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.
Addendums:
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.
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 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 3 | Sun | Mon | Tue | Wed | Thu | Fri | Sat | |||
| 4 | Thursday, January 30, 2020 | January | ||||||||
| 5 | 1 | 2 | 3 | 4 | ||||||
| 6 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | |||
| 7 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | |||
| 8 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | |||
| 9 | 26 | 27 | 28 | 29 | 30 | 31 | ||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A4 | A4 | =TODAY() |
| B4 | B4 | =A4 |
| B5:H10 | B5 | =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 | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B5:H10 | Expression | =ISNUMBER(B5) | text | NO |
Addendums:
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.
