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.
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.
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
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
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:
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:
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
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.
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
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
The final result is your weekly calendar