Calendar Template in Google Sheets (Monthly and Yearly)

You can easily create a simple static calendar template in Google Sheets. However, you would need to update it every time the month or the year changes.

To save you some time (and using the power of Google Sheets), I have created a template that would automatically update the calendar when you change the month or the year.

Monthly Calendar Template in Google Sheets

Below is the demo where the monthly calendar updates when the selection is changed. You can make the month selection from the drop-down list (in the case of months) in cell B2, or manually enter the year in cell B1.

Using Monthly Calendar template in Google Sheets

Click here to access the calendar template. Note that it will ask you to create a copy. You will need to be logged into your Google account to do it. Once a copy is created, you can use the calendar.

Yearly Calendar Template in Google Sheets

Similarly to the monthly calendar, there is a yearly calendar in this template (in a separate tab).

In the yearly calendar, you can enter the year in cell B1, and the entire calendar would update for that year.

To make it more readable, the cells that contain a number are highlighted in a light orange color, while the ones that are empty doesn’t have any background color. This can easily be changed using conditional formatting.

Using Yearly Calendar in Google Sheets

Click here to access the calendar template. Note that it will ask you to create a copy. You will need to be logged into your Google account to do it. Once a copy is created, you can use the calendar.

For the spreadsheet enthusiasts, here is what goes into making these calendar templates in Google Sheets:

  • Date functions: It uses functions such as DAY, DATE, and WEEKDAY.
  • Lookup Function: It uses VLOOKUP to fetch the month’s number which is used in calculations in the calendar. Note that there is a hidden sheet where this function is used.
  • Conditional Formatting: It is used to color the cells that have a day number.
  • Custom Number Formatting: This template uses a smart number formatting technique, where month numbers are displayed as month names. In the yearly calendar template, while you see January, it’s actually 1 in the backend (select the cell and have a look at the formula bar).

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X