• If you would like to post, please check out the MrExcel Message Board FAQ and register here. 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

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


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.
Author
DRSteele
Views
4,752
First release
Last update

Ratings

0.00 star(s) 0 ratings

More Excel articles from DRSteele

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top