How to create a weekly calendar in one formula.

This article is an idea of my fellow Excel MVP Leila Gharani. And she is a great dynamic array functions creator.

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.

Step 2: Write the first month day as header

We can write this formula as header of our weekly calendar

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

mmm yyyy

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

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

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)

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

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

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.

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

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