Create a Dynamic Calendar with function SEQUENCE and some formatting

DRSteele

Well-known Member

Joined

Mar 31, 2015

Messages

2,640

Office Version

1. 365

Platform

1. Windows

• Jan 30, 2020

• #1

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

Click to expand...

Read more about this Excel article...

 

MrExcel

.

Builders Club Member

Joined

Feb 8, 2002

Messages

3,394

Office Version

1. 365

Platform

1. Windows

• Jan 30, 2020

• #2

Nice enhancements! Thanks for writing this up DRSteele!

 

DRSteele

Well-known Member

Joined

Mar 31, 2015

Messages

2,640

Office Version

1. 365

Platform

1. Windows

• Jan 30, 2020

• #3

Thanks for saying so, Bill. You're welcome.

Don.

 

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Jan 31, 2020

• #4

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
	A	B	C	D	E	F	G	H	I
2
3		Sun	Mon	Tue	Wed	Thu	Fri	Sat
4	30/08/2020	August
5		26	27	28	29	30	31	1
6		2	3	4	5	6	7	8
7		9	10	11	12	13	14	15
8		16	17	18	19	20	21	22
9		23	24	25	26	27	28	29
10		30	31	1	2	3	4	5
11
PS

Cell Formulas
Range	Formula
B4	B4	=TEXT(A4,"mmmm")
B5:H10	B5	=SEQUENCE(6,7,EOMONTH(A4,-1)+2-WEEKDAY(EOMONTH(A4,-1)+1),1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:H10	Expression	=TEXT(B5,"mmmm")=$B$4	text	NO

 

Last edited: Feb 1, 2020

smozgur

BatCoder

Builders Club Member

Joined

Feb 28, 2002

Messages

2,571

• Jan 31, 2020

• #5

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!

 

DRSteele

Well-known Member

Joined

Mar 31, 2015

Messages

2,640

Office Version

1. 365

Platform

1. Windows

• Feb 1, 2020

• #6

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!

 

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Feb 1, 2020

• #7

DRSteele said:

And thanks Peter for the super improvement!

Click to expand...

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

 

rjplante

Well-known Member

Joined

Oct 31, 2008

Messages

572

Office Version

1. 365

Platform

1. Windows

• Feb 25, 2020

• #8

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?

 

DRSteele

Well-known Member

Joined

Mar 31, 2015

Messages

2,640

Office Version

1. 365

Platform

1. Windows

• Feb 25, 2020

• #9

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.

 

KevCarter

Board Regular

Joined

Dec 7, 2013

Messages

161

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Feb 26, 2020

• #10

Thank you DrSteele and Peter. Very cool formulas!!!