Calendar Template – Calendar Template

Google Sheets Calendar Template Demo

In this tutorial, we will create a calendar template for Google Sheets. The template will consist of multiple calendar tabs, starting with January and February. You can add more tabs for additional months if desired. For example, you can duplicate a tab and change it to March, updating the year accordingly.

The calendar will automatically update with the corresponding dates for each month. Additionally, the template will include color coding to mark holidays. For instance, if a date is marked as a holiday in the holidays tab, it will be highlighted in blue on the calendar.

To add holidays, simply enter the dates in the holidays tab, and the calendar will automatically mark them with the appropriate color coding.

Furthermore, the template offers the option to add event details to specific dates. This functionality is optional and not necessary for the basic calendar. However, I will cover how to implement this feature in a separate post tutorial.

To demonstrate this feature, let’s take an example for March. If, for instance, on March 9th, there is an event or task, you can enter the details in the designated section, including the date, time, and description. As you enter the information, it will automatically appear on the March calendar tab.

If there are multiple events on the same day, you can add them as separate entries under the details section. Each entry will be displayed as a separate line on the calendar, showing the date, time, and event description.

This tutorial will guide you through building this calendar template, including the holiday color coding and event functionality. So, let’s begin and get started with the tutorial.

Build Calendar

So, we start with an empty spreadsheet. To begin, we’ll enter the days of the week, starting with Sunday. We can merge the cells on top and center the text. Then, we can enter the month and year, such as “J 2021” for January 2021.

Next, we’ll use the WEEKDAY function to determine the day of the week for the first day of the month. By subtracting this number from the date in the top cell, we can calculate the date for each corresponding cell below. To do this, we’ll use the formula A1 – WEEKDAY(A1), where A1 is the cell containing the date.

To generate the following dates, we can use a simple formula: “=previous cell + 1”. This will add one day to the previous date and populate the subsequent cells accordingly. However, since we’ve determined that January 1st should be a Friday, we need to adjust the formula by adding “+ 1” to shift the dates one position to the left.

We can continue this process for subsequent weeks by adding “+ 7” to the previous date formula. By dragging the formula down, we can populate the rest of the calendar.

To switch to a different month, we can change the date in the top cell to the desired month and year. The calendar will automatically adjust accordingly. For example, switching to April 1st will shift the entire calendar to display April 1st as a Thursday.

To format the calendar, we can adjust the row height and center the text. Adding additional space between the rows can make the calendar more visually appealing.

To hide dates that are not part of the current month, we can use the MONTH function to compare the month of each cell to the month of the current date. If they match, the date will be displayed, and if not, it will be hidden.

In December, it will give us 12, and in January, it will give us 1. Since they are not equal, it will return false. However, if the months are the same, it will return true. To ensure the formula works correctly when dragged left or right, we can use an absolute reference by adding dollar signs to the cell reference. This will allow us to use the formula for conditional formatting.

For example, let’s say we have a range of cells with dates, and we want to apply conditional formatting based on whether the date’s month is the same as the reference month. We can copy the formula, select the cell, go to Format, and choose Conditional Formatting. In the custom formula, we can paste the formula and select the desired formatting, such as highlighting or changing the font color.

To remove the default gridlines, you can go to the View menu and uncheck the “Gridlines” option. To add a border around the calendar, select the cells, choose the border tool, and select the desired border style and color.

To apply the formatting to multiple cells, you can copy the formatted border, select the target cells, right-click, choose Paste Special, and select “Paste format only.”

To display the dates as numbers (e.g., 1, 2, 3) instead of the full date (e.g., January 1, January 2), you can select the rows containing the dates, go to Format, choose Number, and select “More formats.” In the custom number format, you can use “d” to display only the day.

Finally, you can format the calendar by adding background colors, adjusting font size, and applying additional formatting options as desired.

I will select a slightly lighter background color for this section and choose a corresponding font color. Then, I will resize this row to make it a bit larger and center-align the content. To add a border, I will use a border color that matches the background and apply it to the entire area. If the color is too light, I will choose a slightly darker shade.

Next, I will change the font for the entire calendar by selecting the upper left corner and choosing a different font. I will make it bold, including the month name. The same border color will be applied to this section as well.

To resize the numbers representing the days of the week, I will select all of them and increase the font size to 11.

For the conditional formatting, I will make the background color a little darker to indicate the selected dates. I will also keep the text color the same to make it invisible. Once the changes are made, I will apply the same formatting to the following cells.

Overall, these adjustments should create a visually appealing calendar. If needed, the date can be changed by modifying the cell or creating a copy of the tab and updating the date accordingly. For instance, I can rename the tab to “January” to reflect the current month.

Add Holidays

At this point, I would like to add some conditional formatting to highlight our holidays. To do this, I will create another worksheet and name it “Holidays.” In this worksheet, I will create a column to list the dates of our holidays. You can continue adding dates as needed.

To apply conditional formatting and highlight the holidays in our calendar, I will use the MATCH function. This function will check if a date in the calendar matches any date in the “Holidays” column. Let’s use the example of checking if the current day matches a holiday. I will enter the formula “=MATCH” and select the current date. Then, I will navigate to the “Holidays” tab, select the entire column (column A), add absolute references with the F4 key, and use a comma followed by a 0 for an exact match.

After entering the formula, I can see that it returns a value of 1. However, the formatting might not be correct because the cell is formatted as a date. To resolve this, I will change the formatting to a number, and now I can see that it returns 2, indicating that the date is found in the second row of the “Holidays” column.

To apply conditional formatting, we can’t directly link to an external tab. Instead, we will use the INDIRECT function. I will modify the formula by wrapping the range in quotes to treat it as text and enclosing it within the INDIRECT function.

Once the formula is modified, we can apply it as conditional formatting. The formula will evaluate to true when there is a match and false when there isn’t. I will copy the formula, select the cell where I want to add the formatting, go to Format, choose Conditional Formatting, add a new rule, select “Custom formula,” and paste the formula. Then, I can choose a background color and font color for the highlighted holiday cell, and optionally make it bold. After making the formatting selections, I will click “Done.”

However, the formatting has only been applied to the one cell, so I need to apply it to all the number cells in the calendar. I will go back to the cell with the applied formatting, copy it, cancel the conditional formatting dialog, and return to the new rule. I will update the “Apply to range” to include all the number cells. After pasting the copied formatting, I will click “Done.”

Now, the conditional formatting should be applied to all the number cells. However, since the first cell in the range has changed, I need to adjust the formula accordingly. I will go back to the rule, modify the formula by changing the reference from F3 to A3 to match the first cell in the range. After making the adjustment, I will click “Done.”

Now, when I change the date to January 4th, for example, both January 1st and January 4th will be highlighted as holidays. This formatting can be applied to other cells as well, depending on the holidays you want to highlight.

I find this approach effective for color-coding the calendar based on holidays. By adding the dates in the “Holidays” worksheet, the calendar will automatically highlight those days. Feel free to continue adding holidays to further color-code your calendar.

Add Event Information to the Calendar

So, at this stage, the calendar is almost ready, and now we can proceed to the next step. We will create a detailed worksheet where we can add events, and these events will automatically appear in our calendar. To do this, we will add another tab named “Details.”

In the “Details” worksheet, we will enter the date, time, and description of each event. To ensure that the events are displayed in the correct order, we will create another worksheet. We will copy the column names from the “Details” worksheet and paste them into the new worksheet. Then, we will use the SORT function to sort the data.

In the new worksheet, select the range starting from the second row and use the SORT function. The SORT function takes the range of data, followed by the sort_column parameter. We want to sort by date first, so we set it to 1 for the first column. Then, set the sort_order parameter to TRUE for ascending order. Next, set the sort_column parameter to 2 for the time column, and again set the sort_order parameter to TRUE. Close the parentheses and press Enter.

Now, the data in the “Details” worksheet should be automatically sorted based on the date and time. Although it may not make a difference initially, as we add more events, the sorting will become evident.

To populate the events in our calendar, we will create a formula using the FILTER function. The FILTER function displays the data based on specific conditions. In the “Details” worksheet, select the cell where you want the events to appear. Then, enter the FILTER function.

Within the FILTER function, select the range of descriptions, dropping the end reference to cover the entire column. Press F4 to make the reference absolute. Next, add a comma to separate the range from the condition.

For the condition, select the date column, drop the end reference, and press F4 to make it absolute. Set the condition to equal the date you want to filter. You can refer to a specific date in the “Details” worksheet. Close the parentheses and press Enter.

You may encounter a reference error because the FILTER function returns a range of data, not a single cell. To see the results, copy the formula and paste it next to the original formula. You will see all the events that match the specified date.

To combine the events into a single cell, we will use the JOIN function. Return to the original formula and add the JOIN function before the FILTER function. Use a quotation mark and press Alt/Option + Enter to add a new line as a delimiter. This will create a line break between each event. Then, add a comma and include the range of filtered data within the JOIN function. Close the parentheses and press Enter.

Now, you should see all the events for the specified date in a single cell. However, only the descriptions are displayed. To include the times as well, modify the formula. Create an array by wrapping the column containing the times in curly brackets. Copy the range and paste it next to the existing range. Use the ampersand (&) to join the two ranges together. Update the column reference to the correct column, which should be column B for the times.

The times may not appear correctly due to formatting. To format the times correctly, use the TEXT function. Add the TEXT function before the column reference. Specify the format in quotes, such as “hh:mm am/pm” for hours and minutes with AM/PM indication.

After making these adjustments, press Enter. Now, you should see the events with both descriptions and times in the single cell.

You can continue adding events to the “Details” worksheet, and they will automatically populate in the designated cell in the calendar, including their descriptions and times.

To address the issue of combining the elements without any separation, we can modify the formula. Instead of directly joining the elements, we’ll add separators between them. Here’s the revised version:

To fix this, I’ll go back and modify the formula. When joining these elements together, I’ll include a separator. I’ll use a space and another ampersand to join the columns. So, if I enter “C,” I’ll get the times and descriptions.

Finally, I’ll add an IFERROR function around the entire formula. This will handle situations where there are no matching events for that particular day. It will display nothing if there are no events.

Let’s update the formula accordingly. Once we have the updated formula, we can copy it and replace the existing formula. Then, we can apply the same formatting and alignment to the cells.

Once we’ve completed these steps, we can hide the extra tab and keep the “Details” tab. Now, when we switch to different months, the corresponding events will automatically populate in the calendar.

If you want, you can dedicate more space to the right to accommodate longer event descriptions. You can also repeat the process for other months by creating duplicate tabs.

Now, you have both January and February calendars with all the data. You can print them and adjust the printing settings as needed, such as using the “Fit to Page” option.

By following these steps, you should have a calendar with properly separated events, and the corresponding details will populate correctly based on the selected month.

Make a Copy of the Calendar Template here:…

Learn how to create a Calendar Template in Google Sheets.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top