Are you looking for the best way to manage your weekly tasks and stay organized?
A weekly calendar consists of days of the week across the horizontal axis and the time of each day broken into hours across the vertical axis, as you might have seen in Google Calendar or Apple Calendar.
Time management is crucial for success.
A Weekly Calendar helps you list what you should do and assign the start time for each.
Why is Google Sheets Weekly Calendar the best choice?
Google Calendar and Apple Calendar are useful if you have one to three daily tasks. But what if you have tens of tasks that must be completed daily throughout the week?
In this case, a weekly calendar designed from scratch in Google Sheets is your way to go.
It boasts more features and can be shared with others for team collaboration.
The Google Sheets Weekly Calendar template is super useful and provides the easiest way to save time and effort.
Let us learn to build a Google Sheets Weekly Calendar in this article. We will discuss all the steps to develop it from scratch in the upcoming section.
Download the FREE Google Sheets Weekly Calendar Template
If you are in a hurry and don’t want to fall into the nuts and bolts of building Google Sheets Weekly Calendar from scratch, you can download the following customizable template we designed.
The first link is a View Only file. To edit it, follow the steps below.
- Open the customizable Google Sheets Weekly Calendar Template
- Hover to the main menu and click on “File” button
- Select the “Make a copy” option from the popup
- A new dialog box will open
- Edit the name of the spreadsheet
- You can also assign a specific location/folder to your weekly calendar through the dropdown below the “Folder” option
- Click the “Make a copy” button in the green
A new window will be opened where you can edit the template to your liking.
How to build a Weekly Calendar in Google Sheets
This section is the step-by-step guide to creating a weekly calendar using Google Sheets from scratch.
We will use the TIME, TEXT, and UPPER functions in Google Sheets. Also, the Data Validation feature and our basic formatting skills.
As you might have noticed from the above image, our Weekly Calendar contains two main sections: Navigator and Table.
In the Navigator section, you can define the week you wish to create tasks and track the progress. Along with the week, you can also specify the time you start your day and the standard time interval between the time of the day.
The second section contains the table, divided into two sections: Horizontal and Vertical.
The Horizontal section holds the day and date of the week, whereas the Vertical section is divided into the time of the day.
For the sake of this article, we will divide our process of creating the Weekly Calendar into four steps as below.
Step #1 – Design the Navigator for the Weekly Calendar
It is a simple step where we will not use any formula. However, let’s only ensure to format each cell and use the Data Validation feature properly.
1. Let us start by creating a new table, as shown below,
(Make sure to use the same headers or at least relevant ones to avoid future confusion.)
2. Under the “Week Start Date” column header, you only need to put the date in a valid format.
We will use the United States date formatting that starts with month followed by day and year (mm/dd/yyyy).
For example, January 8th, 2023, will be represented as 1/8/2023.
The output is displayed above. Refer to the image above.
A small calendar pops up whenever you click on the cell containing the valid date. It is a user-friendly feature in Google Sheets to select the date instead of manually typing it each time.
3. For the time, we will use the Data Validation feature in Google Sheets to create a dropdown containing predefined values.
Add a new spreadsheet using the “+” icon at the lower-leftmost side of the screen. Don’t forget to name this new spreadsheet “Date and Interval Values.”
We will create a table as shown below in the new spreadsheet. Make sure to add the headers for further reference.
The cell values from the first column of this table will act as a data source for our dropdown.
Now let us use the Data Validation and create a dropdown,
- Go back to our main spreadsheet
(In your case, it may be named as “Sheet1”)
- Click on the cell below the “Day Start Time”
- Hover to the main menu and click on “Data” tab
- Select the “Data validation” from the popup
- A new dialog box will open on the right side of the screen, as shown below
- Click on the “Add rule” button
- Next, click on the dropdown below the option “Criteria”
- Select the “Dropdown (from a range)” option from the list
- A new blank box will be displayed as shown below
- Click on the table icon. Refer to the following image
- Now, you will see a new popup as shown below
- Go to the “Time and Interval Value” spreadsheet
- Use your mouse to drag and select all the cells from the Time column
- Click on the “OK” button on the popup
- You will see that all the values are populated across the “Data validation rules” window
- Scroll down and click on the “Advanced options” button at the bottom of the “Data validation rules” window
- Now, change the default settings as below
- Click on the “Done” button in the green
Now, you can go back to your main spreadsheet and see a small dropdown icon displayed in the cell below the column header “Day Start Time”.
Don’t forget to change the cell’s formatting to hour and minute, as shown in the following image.
To change the time format, you need to go to the “Format” tab, then select “Number” followed by “Custom date and time”. Refer to the following image.
A new popup will appear. Choose the proper time format from the list and click the “Apply” button in the green.
4. Similarly, for Interval, we will create a dropdown using the Data Validation feature to reflect 15-minute intervals for each hour.
Note that we already created a list in the “Time and Interval Value” spreadsheet for the interval dropdown, as shown below.
All the steps to create this dropdown are similar, and you just need to make sure to select the values from the “Interval” column of the spreadsheet “Time and Interval Values” in step number 12. Refer to the following GIF.
That’s it. Our Navigator section of the Weekly Calendar is done.
It should look like below,
Please ensure you are using the proper date and time format if your Navigator section differs from ours.
Step #2 – Create the Horizontal Section of the Table
We need two rows, the first for the day and the second for the date, as shown below.
All the cells from the above two rows are dynamic. They are calculated based on the value in the cell B3.
We will also use the TEXT function in Google Sheets. The general syntax is as follows,
Here is how to deal with each argument of the above formula,
- “number” – It can be a text string, number, or date. So, here we need to put our date
- “format” – It is a versatile argument that accepts various inputs for generating specific outputs, including Month, Date, Year, and more
Note that Cell B3 contains the Week Start Date. Let’s use this data to generate the weekdays and populate them across the Horizontal section.
1. Start by selecting the cell “B7” where our first date of the week will be displayed.
The value in “B7” should be similar to the value in “B3” defined by the users.
Let’s use the equal (=) operator to match the cell values of B7 and B3.
Here’s how the final formula looks like,
Copy and paste the above formula in B7 to get the users’ exact date provided in cell B3.
2. Now, we will use the Text function to get the day from the date.
- Select the cell “B6”
- Type “=text”
- Choose the first option from the popup or press “Tab” on your keyboard
- Now, select the cell reference as “B7” as our date values are in B7
- Press “,” on your keyboard to move to the next argument
- Next, put “dddd” in the place of the “format” argument to get the day, and make sure you will use the double quotation marks as shown below
- Complete the bracket using “)”
- Press the “Enter” key
Here is the formula,
It will instantly display Sunday as the starting date of the week using the data available in cell B6.
3. For the next date, which is going to be our second of the week, as shown below, we will increment the value in “B7” by “1”.
Our formula will be as follows,
Copy and paste this formula into the cell C7.
4. To get the name of the second day of the week, we will use the TEXT function, as discussed in step 2 of this section.
Make sure to use the proper cell reference value (C7)
5. Repeat the steps 3 and 4 to get the remaining dates and days of the week.
Refer to the following image, where I have displayed all the formulas for getting the date and day of the week for our Weekly Calendar.
You can change the date format to something like below,
Go to the “Format” tab of the main menu, choose “Number” from the dropdown, followed by “Custom date and time”.
A new dialog box will open. Choose the desired date format from the list and click the “Apply” button in the green.
The final output will be as follows,
Step #3 – Create the Vertical Section of the Table
The vertical section is where the days of the week are divided into hours.
The values here depend upon the “Day Start Time” and “Interval” defined by the users.
Let us quickly discuss the TIME function in Google Sheets.
The TIME is a simple function by Google Sheets with the following syntax,
=TIME(hour, minute, second)
All three arguments are self-explanatory here. So, we will not discuss them separately. But note that to replace these arguments, you can either manually enter the values or provide the cell reference containing the same.
Also, the TIME function accepts integer values only. If there will be any one of the cell contains the text string, the function won’t work and returns the error as shown below,
Let’s use the time function to get the time of the day (vertical section) column.
1. The first cell’s value (A8) is supposed to be the same as C3, where users select the Day Start Time.
We will use the Equal operator to execute this task. Our final formula will be,
Copy and paste the above formula in A8.
This way, whenever the user selects the Day Start Time in cell C2, it will be reflected in A8.
Again, you need to make sure the formatting is proper. To change the formatting, go to the “Format” tab of the main menu and click on “Numbers” followed by “Custom date and time”.
A new dialog box will be displayed. Choose the proper time format from the list and click the “Apply” button.
2. As you might have noticed from the table, we are not directly dividing our day into single hours. Instead, we use 15, 30, and 45-minute intervals for each hour.
Refer to the following image.
For this logic to work for our vertical section, we will increment the time in cell A9 by the interval defined by the user.
We will use the TIME function here.
IMPORTANT – Necessary Adjustment
But before proceeding further, we need to make an important adjustment.
As mentioned earlier, the TIME function accepts integer values only. If you check the formatting of cell D3, you will notice that it is a plain text format due to the text “MIN” in the cell.
So, to get the integer number for the interval, we will use the cell D4 right below the D3. Refer to the following image.
Copy and paste the formula in cell D4 to get an integer number from D3.
The LEFT function will extract the first two digits from the cell D3.
Now that we have an integer value for the interval let us use the TIME function to get incremented time in cell A9.
As you notice, we have put “0” for hours and seconds. The minutes are taken from cell D4, where we converted the text string from D3 to an integer number.
The results will be as follows.
Make sure to change the cell’s formatting, as we discussed in the previous step of this section.
3. Repeat the step 2 for remaining cells. Make sure to choose the lowest time interval to create the vertical section of the table.
For the sake of this article, I have assumed the time of the day till 6:00 PM only. You can go beyond that by following step 2.
Here’s how the formulas will look,
Our final output will be as follows,
Step #4 – Checking the Formatting
It is not the essential step, but it will surely help you improve your Weekly Calendar aesthetics and ease of use.
The navigator section should look similar to the following,
Make sure to properly change the cell background color (green) and text color (white).
Change the Date format in cell B3 and the time format in cell C3.
Here, we can change the background color for each day of the week.
Make sure to use the proper formatting for the day and date. We have already discussed using the appropriate date and day format in steps 1 and 2 above.
This section contains time only. So, just make sure to use the proper formatting to display the time, as discussed in step 3.
Here is the screenshot of the output of the Weekly Calendar we designed from scratch by following the above steps.
If you wish to download the Google Sheets Weekly Calendar Template. Click Here!