• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

Create a Dynamic Calendar with function SEQUENCE and some formatting

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
51234
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
Excel Version
365
Author
DRSteele
Views
277
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

Some videos you may like

This Week's Hot Topics

Top