DRSteele

Create a Dynamic Calendar with function SEQUENCE and some formatting

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,659
Office Version
  1. 365
Platform
  1. Windows
DRSteele submitted a new Excel article:

Create a Dynamic Calendar with function SEQUENCE and some formatting - Using Excel's new dynamic array formulas, we can easily generate a calendar based on a date

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...

Read more about this Excel article...
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Nice enhancements! Thanks for writing this up DRSteele!
 
Thanks for saying so, Bill. You're welcome.

Don.
 
Using that as a starting point, I think we can use a considerably simpler sequence formula?

Format a block of 42 cells (7Cx6R), here B5 to H10, using Conditional Formatting (Outline border and black font) with the CF formula as shown below. Format B5 to H10 as custom number format as d and with white font. A4 whatever date format you want.

Peter_SSs Calendar Test.xlsm
ABCDEFGHI
2
3SunMonTueWedThuFriSat
430/08/2020August
52627282930311
62345678
79101112131415
816171819202122
923242526272829
10303112345
11
PS
Cell Formulas
RangeFormula
B4B4=TEXT(A4,"mmmm")
B5:H10B5=SEQUENCE(6,7,EOMONTH(A4,-1)+2-WEEKDAY(EOMONTH(A4,-1)+1),1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:H10Expression=TEXT(B5,"mmmm")=$B$4textNO
 
Last edited:
I really started enjoying Dynamic Array Formulas even I found how it works a bit strange at the beginning. Thanks for the article @DRSteele and thanks for the improvement @Peter_SSs! This is a great power in Excel!
 
Smozgur, I'm glad you like the Calendar! It's great fun, these new functions and their dynamism. Very powerful. You'll rapidly get used to how the spilling technology works. And you'll rapidly get annoyed that the formatting does not spill along with the formulas.

And thanks Peter for the super improvement!
 
And thanks Peter for the super improvement!
(y)


For those that like shorter formulas (though no fewer functions here)
=SEQUENCE(6,7,EOMONTH(A4,-1)+2-WEEKDAY(EOMONTH(A4,-1)+1),1)
could be replaced by
=SEQUENCE(6,7,A4-DAY(A4)+2-WEEKDAY(A4-DAY(A4)+1))
 
I have created your dynamic calendar in an excel spreadsheet and it is pretty cool. I have added formatting to the Month in cell B5 so that it is formatted as mmmm - yyyy. I was wondering if there is a way to use conditional formatting to highlight the day on the calendar that matches the day in the date entered into A4. For example if 2/25/2020 is added to A4, the cell with 25 in it would be highlighted and the cell would be filled with a color of your choosing or outlined in a thick red border. Is it possible to do this?
 
Sure RJ, we can do that.

Highlight cells B5 to H10. Create a new CF rule that has the cell value =$A$4 and then select your formatting. That way, the Calendar will have the current day formatted.

Annotation 2020-02-25 153112.jpg
 

Forum statistics

Threads
1,224,914
Messages
6,181,720
Members
453,064
Latest member
robatthe2A

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