Weekly calendar 2020 in one formula

Discover how to create a weekly calendar for 2020 in just one formula with Office 365. This article is an idea of my fellow Excel MVP Leila Gharani. And she is a great dynamic array functions creator.

Weekly calendar 2020 - All months

Are you an Office Insider?

To create a weekly calendar with only one formula, you will need to use the new SEQUENCE function. This function is only available if you are an Office Insider.

When you are an Office Insider, you have access to specific functions, like dynamic arrays. And this is what we need here.

Change the level of your Office Program

When does your weekly calendar starts?

The specificity of a weekly calendar is to always start a Monday or a Sunday. So need to build a formula that returns always the last Monday or Sunday of the previous month (if the month doesn't start Monday or Sunday)

Step 1: Create the formula for the first day

To calculate the first day of any month, we need the function DATE

=DATE($C$2,$C$3,1)

If you customize the date format, you can see that the 1st January 2020 is a Wednesday. And if you change the format to Standard, you have the number 43831. This information is important for the next part.

Calculation of the first day of the month

Step 2: Write the first month day as header

We can write this formula as header of our weekly calendar

First day of the month as header

Of course, the format must be customized to return the month name and the year with this format

Menu to open the custom format

mmm yyyy

Customize the date format to display the month and the year

Step 3: Change the formula to start always on the same weekday (Sunday, Monday, ...)

If you want to start your weekly calendar on Sunday, we must use the WEEKDAY function and the IF function because, we have 2 situations:

  • The first day of the month is a Sunday, WEEKDAY(B5,2) = 7, so our calendar will start on the first day
  • Otherwise, we reduce the first day of the month calculated in B5 by the number of weekdays to reach Sunday.

So, the for Sunday it is:

=IF(WEEKDAY(B5,2)=7,B5,B5-WEEKDAY(B5,2))

Calculation of the previous Sunday

Now, November 1, 2020 is a Sunday. So, in this situation, the formula returns to 01/11/2020

The formula returns the first day of the month

If your weekly calendar starts on Monday, the formula is:

=IF(WEEKDAY(B5,3)=7,B5,B5-WEEKDAY(B5,3))

Step 4: Generate the other days with SEQUENCE

Now, we need to write all the other days of our weekly calendar. This is possible with the dynamic array formula SEQUENCE. This function has 4 arguments

  • The number of rows (6 to cover all the situation)
  • The number of columns (easy it's 7, 7 days in a week)
  • The starting value is the previous formula
  • And the step, 1.

=SEQUENCE(6,7, IF(WEEKDAY(B5,2)=7,B5,B5-WEEKDAY(B5,2)) ,1)

Calendar generated with the function SEQUENCE

Now, to understand how this is possible, we just must change the number format to Standard to see the list of numbers generated by the function SEQUENCE

Values returns by the function SEQUENCE in Standard format

Step 5: Customize the format of the date

In this article, you will see all the explanations on how to change the date format. Here, we want to display only the value of the day. So, in the custom setting of the number format we will write only the value d

d

Code to return the day of a date

And now we have these value in the weekly calendar

Step 6: Change the color of the day in or out the month selected

In order to have a better visualization of the days of the selected month, we will change the colors of the days with conditional formatting.

Menu conditional formatting

Rule for the day of the selected month

The first rule to test if the dates are in the selected month is to compare if the month of the date in B7 is equal to the month number in C3. The format to apply is a font color black and bold text.

=MONTH(B7)=$C$3

Rule for the days in the selected month

The $ to block the references or not is very important here. MONTH(B7) is a relative reference to read all the cells of our calendar and $C$3 is blocked on the selected month

Rule for the other days, those not in the month

The formula to highlight the days not in the selected month is very easy

=MONTH(B7)<>$C$3

The format to apply in this situation is a font color gray.

Rule for the weekend days

Third rule, the color of the day is red and bold. So the formula is

=AND(WEEKDAY(B7,2)>5,MONTH(B7)=$C$3)

The final result is your weekly calendar

Weekly calendar

Related posts


Permanent link to this article: https://www.excel-exercise.com/weekly-calendar-2020-in-one-formula/

Leave a Reply

Your email address will not be published.