A calendar view template in Google Sheets can help you view your data (records) in an easy-to-understand way.
In Google Sheets, we can create and view reports in tabular form using functions like FILTER, QUERY, or Pivot Table.
However, the custom calendar view mode in Google Sheets displays the information in a standard calendar format based on the user-selected month and year, and other settings within the source data sheet.
You can navigate through the calendar by month and year.
The corresponding data will be displayed under each date in the selected month and year, depending on the availability of data in the source sheet.
You can use my calendar view template in Google Sheets to view sales data, purchase data, employee punch data, to-do lists, track your events and meetings, and more.
Features
The custom calendar view template in Google Sheets supports different data types. They are text, numbers, time, and duration.
If multiple records are present for a date, the data will be aggregated based on the data type.
Numbers and durations will be aggregated (summed). Texts will be made unique and then concatenated.
Combining multiple columns is also supported.
It is useful when you have addresses entered in columns, such as name, email, phone number, etc., in their own columns.
We will see more about the above features in the example section below. Before that, you can download (make a copy of) my calendar view template below.
Using a Custom Calendar View Template in Google Sheets
The template file has two sheets: Master and CV.
The Master sheet contains nine preset fields (columns):
- Column 1: Date
- Columns 2 to 6: Text
- Columns 7 and 8: Time
- Column 9: Duration or number
We will only fill in the data in the necessary columns. The CV sheet will automatically update to reflect the data in the Master sheet.
Note: The values entered in columns 2 to 6 will be combined into a single value in the calendar view.
Let’s see three examples.
1. Viewing the Daily Time Calendar of an Employee
As we saw above, the Master sheet is formatted to support different data types. We will start by entering the punching data of an employee.
To populate a daily time calendar of an employee, we need three fields: Date, Start Time, and End Time.
We will fill the data in columns A, G, and H, which are columns 1, 7, and 8, respectively.
Remove the field labels in the unused columns B1:F1 and I1.
Here is the calendar view of the employee in and out data that we entered in the Master sheet.
Notes:
- If an employee has multiple (in and out) records for the same date, the earliest time will be considered as the time in and the latest time will be considered as the time out.
- You should manually enter the texts “Start” and “End” in column A in the CV sheet, in the respective rows. Please refer to the image above.
2. Calendar View of Appointment Log in Google Sheets
In this example, the data to be processed in calendar view is an appointment log in Google Sheets. So the desired input will include the following fields (columns):
- Date (Column 1)
- Name (Column 2)
- Email (Column 3)
- Phone (Column 4)
- Meeting Location (Column 5)
- Attendees (Column 6)
- Time (Column 7)
Here is how to fill in the relevant information in our Master sheet:
Once you have completed entering your data in the Master sheet, go to the CV sheet to see the results.
Here is the calendar view of the appointment log in Google Sheets.
3. To-Do List in Calendar View in Google Sheets
Having a to-do list is a good way to prioritize your personal or work-related tasks.
If you have maintained or are planning to maintain a spreadsheet for a to-do list, using Google Sheets is a good idea.
Do you know why?
It is because Google Sheets is a cloud-based spreadsheet program that you can access from your mobile or computer at home or work.
You can use my template to enter your to-do list and view them in the calendar view.
Here are the steps on how to use my calendar view template for a to-do list in Google Sheets:
In the Master sheet, enter the task dates in column A and the tasks in column B.
Go to the CV sheet and select the month in cell G1 and the year in cell H1.
The to-do list will be readily available to view.
Google Sheets Calendar View Template: Key Formulas
We have several array formulas in the CV sheet. They are in the range B5:B34. Part of them are for populating the dynamic calendar and those formulas are in cells B5, B10, B15, B20, B25, and B30.
Between these cells we have the formulas that retrieve the values for calendar view and here are those key formulas.
B6: The following formula in cell B6 retrieves the text values from columns B to F (2 to 6) from the Master sheet.
=ARRAYFORMULA(LET(source_dt,Master!$A$2:$A,source_txt,Master!$B$2:$F,header,Master!$B$1:$F$1,month_txt,$G$1,calendar_dt,B5:H5,MAP(calendar_dt,LAMBDA(dtd,TEXTJOIN(CHAR(10),TRUE,UNIQUE(IFNA(FILTER(IF(LEN(source_txt),header&": "&source_txt,),DATEVALUE(source_dt)=dtd,MONTH(source_dt)=MONTH(month_txt&1)))))))))It uniques and combines texts.
B7: The following formula in cell B7 retrieves the time values from column G (7) from the Master sheet.
=ARRAYFORMULA(LET(source_dt,Master!$A$2:$A,numeric,Master!$G$2:$G,month_txt,$G$1,calendar_dt,B5:H5,MAP(calendar_dt,LAMBDA(dtd,SORTN(IFNA(FILTER(numeric,DATEVALUE(source_dt)=dtd,MONTH(source_dt)=MONTH(month_txt&1))))))))It returns the earliest time when there are multiple entries for the same date.
B8: The following formula in cell B8 retrieves the time values from column H (8) from the Master sheet.
=ARRAYFORMULA(LET(source_dt,Master!$A$2:$A,numeric,Master!$H$2:$H,month_txt,$G$1,calendar_dt,B5:H5,MAP(calendar_dt,LAMBDA(dtd,SORTN(IFNA(FILTER(numeric,DATEVALUE(source_dt)=dtd,MONTH(source_dt)=MONTH(month_txt&1))),1,0,1,0)))))It returns the latest time when there are multiple entries for the same date.
B9: The following formula in cell B9 retrieves the duration or numeric values from column I (9) from the Master sheet.
=ARRAYFORMULA(LET(source_dt,Master!$A$2:$A,numeric,Master!$I$2:$I,month_txt,$G$1,calendar_dt,B5:H5,result,MAP(calendar_dt,LAMBDA(dtd,IFNA(SUM(FILTER(numeric,DATEVALUE(source_dt)=dtd,MONTH(source_dt)=MONTH(month_txt&1)))))),IF(result>0,result,)))It returns the aggregated (sum) value when there are multiple entries for the same date.
Copied the above formulas in cells B6 to B9 and pasted them in cells B11, B16, B21, B26, and B31.
Conclusion
I’ve given three examples to show you how to use my calendar view template in Google Sheets.
You may have noticed that none of them use the 9th duration/numeric column (I) in the Master sheet. Here’s how to use it.
Assume you want to show your daily expenses in the calendar view.
Enter dates in column A, expense head (description) in column B, and amount in column I. If there are multiple expenses on the same date, you can treat them in two ways:
- Enter them in two rows (assume there are two expenses). This will cause two records on the same date. The corresponding formula will merge (aggregate) them.
- Enter them in one row. Enter the descriptions comma-separated and manually add up the expenses and enter the total in column I.
Similarly, you can use the ninth column for entering duration, such as overtime. You can do this yourself once you’ve gone through the above three examples.
That’s all about the calendar view template in Google Sheets. I hope you enjoyed it.
Other Popular Custom Templates:
 in an easy-to-understand way.){kind=link}