Hi! This is the last of 3 posts culminating in a dynamic yearly calendar. You can use this calendar as a content calendar or just to mark off dates while working on your computer. The calendar is dynamic because it will update automatically whenever you change the year. If you are reading this blog for the first time, check out this, then this to catch up. The methods used in this post work in both Excel and Google Sheets.
Before we get started, let's look at the formula we will use to build the calendar:
Building the calendar
To build the calendar, there are a few details we must consider:
A year has about 52 weeks
A week has seven days
A month can begin on any day of the week
We will use a phone calendar to check our calendar for accuracy
Set the year and days of the week
We will enter the year at the top of the spreadsheet in any cell, then enter the 7 days of the week horizontally starting with Sunday:
In the cell below Sunday, this is where we will type our formula which will create our entire calendar. As a reminder, SEQUENCE takes 4 arguments: rows, columns, start, step:
Let's figure out what to enter for each argument, starting with rows
A year has about 52 weeks and therefore it would make sense to write 52 rows; however, since we don't know what day our year starts on or ends, we use 53 rows to ensure we don't miss any days. This might be confusing, so let me explain it further: Take out your phone calendar and take a look at all the months of the year at a glance. Here is a screenshot from mine:
As you can see above, even though we say a month has 4 weeks, these 4 weeks do not always fit nicely on 4 rows as we see in February, and a month can begin and end on any day of the week. If we stacked these months on top of each other, they would likely occupy more than 52 rows in Excel.
To make sure we get all the days on our calendar, we add 1 more row to make sure all the days fit on our calendar. We can use 54 to be safer; it does not matter. Even the iPhone calendar displays more than 52 weeks:
We use 53 rows for 53 weeks, so our formula now looks like this:
Columns
Look at your phone calendar again. The 7 days of the week are equal to 7 columns. Our formula is now:-
Start
The starting point of our calendar should be 1st January 2021. We can use the DATE function to give us that date. Here is what the DATE function looks like:
Remember, we are typing our formula in the cell below Sunday (SUN), so while we know when our year starts, we do not know if it starts on Sunday. A quick glance at our phone calendar tells us 1st January 2021 was a Friday. So how do we enter the right date for Sunday (27th December 2020) and ensure our year starts 5 days later on Friday? This is the trickiest part of this entire exercise. Follow closely:
Let's bring back the final formula, but this time, I am highlighting the section of it that represents the start argument:
Breaking down the formula used in the start argument
Here is the formula we need to break down:
DATE(D1,1,1)-WEEKDAY(DATE(D1,1,1))+1In case "D1" is throwing you off, remember the year 2021 is in cell D1 in my spreadsheet. We want 27th December 2020 to fall on Sunday so that we can place 1st January 2021 on the correct day (Friday). We need the DATE and WEEKDAY functions.
The WEEKDAY function returns a numerical value representing the day of the week for a specific date. By default 1 is Sunday and 7 is Saturday. To show you how WEEKDAY works, I use my birthday below:
Now that you know when to send me gifts, let's get you a calendar so you can draw a love heart around my birthday.
Using WEEKDAY in the formula below, we find out that 1st January 2021 was on the 6th day of the week (Friday):
The formula returns the number 6 for Friday. Therefore, since we know Sunday (27th December 2020) is 5 days before Friday (1st January 2021), we subtract the 6 we get from WEEKDAY(DATE(D1,1,1) from 1st January 2021 or DATE(D1,1,1), and add 1 to ensure we are subtracting 5 days (not 6).
Therefore, the formula below takes 01/01/2021(DATE(D1,1,1) and subtracts 6 (WEEKDAY(DATE(D1,1,1)) (because Friday is the sixth day of the week) and adds 1 (because Sunday 27th is 5 days before Friday 1st)
DATE(D1,1,1)-WEEKDAY(DATE(D1,1,1))+1Step
With the step argument, we determine the increment we want to use. In this case, we want to increase by 1 as we count from day 1 to day 365. So our formula is now complete:
Once we press enter, our entire yearly calendar is created! You can change the year by typing a different year in place of 2021 and watching the calendar update. You may have to change the format to Custom (dd-mmm) in case the default values are numbers. Also, for the year, always refer to the specific cell where you entered it instead of writing it manually in the formula.
Homework
We have our calendar, but see if you can format it so it looks somewhat professional. Here is what mine looks like:
Conclusion
You could use this calendar for content management, project management or just to brag about creating a calendar in Excel. I hope this adds value to you. Let me know your thoughts in the comments.
Have a blessed week!
Comments