Creating a Dynamic Yearly Calendar

Creating a Dynamic Yearly Calendar

 

Using SEQUNCE, creating a dynamic yearly calendar is a piece of cake.

                              The Calendar

What it takes is only 5 easy steps:

1.     Add the year parameter (“yyyy”) in cell K1

 

2.     In cell A1, write the following formula:

=TEXT(WEEKDAY(SEQUENCE(,7)),"b1ddd")

 

3.     in cell A2, write the following formula:

 

=LET(d, DATE($K$1,1,1),SEQUENCE(54,7,1-WEEKDAY(d)+d))

 

4.     Select cells A2:G2, go to Conditional Formatting and add the following rule:

 

=A2<DATE($K$1,1,1)

 

Then, press the Format button, and in the Format cells pane, select Font and choose White background from the Color Dropdown

 

5.     Select cells A54:G55, go to Conditional Formatting and add the following rule:

 

=A54>DATE($K$1,12,31)

 

Then, press the Format button, and in the Format cells pane, select Font and choose White background from the Color Dropdown (same as in step 4.)

 

 

Conditional Formatting to hide unnecessary cells

 

That’s all.
Now you have a perfect calendar. If the year does not begin on Sunday, then the cells on row 2 preceding January 1^st will be empty.

The same applies to the end of the year: dates that occur after December 31^st will be empty.

 

 P.S.:

If you wonder why I used 54 weeks (instead of 53 weeks) in my solution, the reason is this:
A leap year has 366 days. So if it starts on Saturday and ends on Sunday, then we have 54 weeks. This happened, for example, in year 2000.