Attendance sheets are used in a number of industries and organizations to keep a record of the turnout, and for effective attendance management. In schools and colleges, teachers use attendance sheets to make sure students attend classes regularly and to detect problems in case of anomalies in a student’s attendance.
Besides the education industry, attendance sheets are also used in companies to track employee attendance and consolidate information for ACRs, salary payment, etc.
Gone are the days when you manually had to enter attendance in a big register and then count them out at the end of the month. With modern spreadsheet software, keeping track of attendance is quick and easy.
Moreover, it automates the process, so once you have an attendance template ready with all the required formulae, you can re-use it every month, without having to re-do calculations each time.
In this tutorial, we will show you how to make a simple Google Sheets attendance template, using some simple Google Sheets formulae.
What Does an Attendance Template Consist of?
An attendance template or Attendance tracker in Google Sheets, consists of a grid where details about attendance of a group of people is recorded. Here the group of people may consist of students in a class, employees in a department, or guests at an event.
Attendance is usually marked with an initial, such as ‘P’ for Present, ‘A’ for Absent, ‘L’ for Late, ‘E’ for Excused, etc.
Other details such as class, group, or department name, and in some cases, time and date may also be recorded in the attendance sheet.
How to Create a Google Sheets Attendance Template
To create an attendance spreadsheet, we start by creating a basic skeleton, or outline of the sheet, in which we create slots for days in the month, student names, and other basic details such as Class / Department / Group, names, dates, etc.
We also put basic conditional formatting into place to highlight weekend days in a different background color, or font. This will ensure that attendance entries are not entered into the weekend slots by mistake.
Once the basic skeleton is ready, we can start filling in the basic details such as names, date, etc. We will also put formulae in place to count the number of present, absent, and other types of initials. At this point, we can also add a function to calculate the attendance percentage for each person.
Once the sheet is ready, we can start filling in the attendance grid with the appropriate initial for each student on each day of the month.
In this tutorial, we will show you step by step how to create attendance sheets. This attendance sheet will automatically count the number of days present and absent, as well as the percentage attendance at the end of the month. You can choose to further customize the sheet to suit your requirements.
Creating the Basic Outline of the Attendance Template
Let us start with the main outline or skeleton of the template.
We’ve done the hard work for you and made a downloadable Google Sheets attendance template that is free to use.
To use this template, click File->Make a copy. This will let you edit and save the sheet as your own.
Understanding the Attendance Template Outline
We made the outline quite simple and basic. It consists of a column for each of the following:
- Serial number
- Number of days present
- Number of days absent
- Percentage attendance
There’s also a slot for you to enter the month. This field has been formatted to display the month and year in the format “mm-yyyy”. So even if you enter a full date, it will get automatically formatted to the month-year form.
To set the format for this field, follow the steps below:
- Select the cell where you want the month to be entered.
- From the Format menu, select Number.
- Scroll down to ‘More formats’.
- Select ‘Custom number format’.
5. This opens the ‘Custom number format’ window. In the input box on top, type the format “mm-yyyy”.
6. Click the ‘Apply’ button.
Your specified format will now be applied to the selected cell.
Filling in the Sequence of Days in the Month
The outline also contains separate columns for each day of the month.
This has been automated to show the number of days in a month, depending on the date entered in cell D2.
So, if the date in D2 is 01-2021, then you will have columns F6 to AJ6 displaying days 1 to 31, since January has 31 days. Similarly, if the date is 04-2021, then you will have columns F6 to AJ6 displaying days 1 to 30, since April has 30 days.
Here’s the formula we used in cell F6 to display the date, sequenced and customized according to month:
The above formula uses the EOMONTH function to find out the last calendar date of a specified month. EOMONTH is basically an abbreviation of ‘End of Month’. So when we pass the date in cell D2 as an argument of this function, we get the last date in the month of that date.
The second argument of this function specifies the number of months before or after the given month. Since we only want the last day of the current month, we specified this argument as 0.
So, if D2 contains 04-2021, then EOMONTH(D2,0) will return 4/30/2021, because that is the last day of April 2021.
Next we wrapped the DAY function around the EOMONTH function to get only the DAY value of the returned date. So, if the EOMONTH function returns 4/30/2021, DAY(EOMONTH(D2,0)) returns 30.
After that, we used the SEQUENCE function to display an array of sequential numbers horizontally from 1 to DAY(EOMONTH(D2,0)). In other words, if the date in D2 is 04-2021, then SEQUENCE(1,DAY(EOMONTH(D2,0))) will display numbers 1 to 30 in sequence horizontally from cells F6 to AI6.
Filling in the Days of Week According to Month
Just below the days in the month, the outline also has separate columns for the day of the week.
This has also been automated to show the day of the week corresponding to each day (of row 6), depending on the date entered in cell D2.
So, if the date in D2 is 04-2021, then you will have columns starting from Thursday, since April 1st 2021 is a Thursday.
Here’s the formula we used in cell F7 to display the day of the week, sequenced and customized according to month:
The above formula extracts the YEAR and MONTH values from the value of cell D2. It gets the DAY value from cell F6. It then combines the three into a DATE value and then uses the WEEKDAY function to extract the day of the week corresponding to the returned DATE.
The TEXT function then formats the day of the week returned to be displayed in the format “ddd” or in the short form containing the first three letters of the day of the week.
So, if D2 contains the date 04-2021, then here’s a breakdown of what the above function returns:
If you drag this formula to copy it horizontally to all the cells till AG, you get the days of the week corresponding to the first 28 days of the month.
Now, we know that a month can have either 28, 29, 30 or 31 days. So the last 3 days may or may not need to be displayed, depending on the month.
For example, in February, you only need to display days till the 28th. On a leap year, you would need to display dates till the 29th. If the month contains only 30 days, you don’t need to display the day of the week corresponding to the 31st of that month.
So in the last 4 cells of row 7 (cells AH: AJ), we need to specify a different formula. This formula should display a blank if there is no corresponding day in row 6. To ensure this, we use the same formula to display the day of the week, but we now wrap an IF statement around it to check if there is a corresponding day in row 6.
The formula in cell AH is:
This formula checks if cell AH6 is blank. If so, then it returns a blank. If not, it returns the day of the week corresponding to the day in cell AH6..
Copy this formula by dragging the fill handle to the right up to cell AJ7.
Using Conditional Formatting to Highlight Weekend Columns
Another thing that you will notice in the outline is that the weekends have been highlighted with a light blue background.
This was done using Conditional Formatting.
Let us now go over the steps to accomplish this kind of formatting:
- From the Format menu, select ‘Conditional Formatting’.
- This opens the Conditional format rule window as a sidebar on the right.
- Under ‘Apply to Range’, type the cell range F7:AJ.
- Click on the dropdown menu under ‘Format cells if…’, and select ‘Custom formula is’.
- A new input box should appear just below the dropdown. Enter the formula: =OR(F$7=”Sun”,F$7=”Sat”) in this input box.
- Under ‘Formatting style’, select the formatting you want to apply to the cells containing weekend days. We simply ensured that the fill color for the cells is set to light blue, which is the default color.
- Click Done.
You will notice all the columns corresponding to Saturdays and Sundays are highlighted in light blue.
Explanation of the Formula
Let us take a moment to understand the formula that we used in the conditional formatting window:
This formula checks if the value in row 7 of the column corresponding to each cell (in the range F7:AJ) has the value “Sun” or “Sat”. Only the cells that qualify this criterion are highlighted in blue.
Calculating Total and Percentage Attendance
Finally, let’s look at columns C, D, and E of the outline.
Column C counts the number of days the student was present in the current month. The formula we used to calculate this is:
This formula will check all cells from F8 to AJ8 and count only the cells that contain the value “P”.
Note that this is case-insensitive, so whether you use “P” or “p”, both are considered in the count.
Column D counts the number of days the student was absent in the current month. The formula we used to calculate this is:
This formula will check all cells from F8 to AJ8 and count only the cells that contain the value “A”.
This is again case-insensitive, so whether you use “A” or “a”, both are considered in the count.
Column E calculates the attendance percentage of the student. The formula we used to calculate this is:
This formula divides the number of days present by the total number of working days in the month and multiplies that by 100 to get the percentage.
The COUNTA function counts only the cells in the given range that have a value. So it ignores all weekends and considers only working days (because these days have a value recorded, be it “A”, “P” or anything else).
Filling in the Attendance Template
Now that the outline is done, all that’s left to do is enter the serial numbers and names.
Once that is done, your template is complete.
Each month, all you need to do is make a new copy of this template, enter the month and year in the format “mm-yyyy” in cell D2 and then enter attendance for each student each day.
The template automatically calculates the number of days absent, present, as well as the percentage attendance for each student.
Customizing the Attendance Template
If you’re using this template to keep track of employee attendance, you might need to include other options like paid leave, casual leave, etc.
If you’re using it for school attendance, you might need to include options for late arrival, medical leave, etc.
This template is meant to be a general purpose attendance sheet, which can be applied to any setting.
You can go ahead and customize the template according to your requirements after making a copy of it.
So, if you want to include an option for medical leave, for example, you can add a new column after D to count the number of medical leave days.
You can use the same formula that you used to count the number of days present, by just replacing the string in the second argument. So if you’re representing medical leave by the letter “M”, your formula would then be:
None of the rest of the cells in the sheet will get affected by this customization.
Another customization you might need could be the weekend days. Some countries have different days allotted as the weekend. So if, say, you have the weekends on Fridays and Saturdays, you could simply change the text in the Conditional formatting formula to:
This was quite an in-depth article, but we tried to make it as detailed as possible so that you can use it to create your own Google Sheets Attendance template, along with your own personal spin.