How to create a weekly calendar in one formula with the functions of Excel 365.
This article is an idea of my fellow Excel MVP Leila Gharani.
The specificity of a weekly calendar is to always start on a Monday or a Sunday. So need to build a formula that always returns 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 of the weekly Excel calendar
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 of 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 the header
We can write this formula as the 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 for changing 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 of 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.
The 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
The 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.
The rule for the weekend days
The 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
