Creating a Fully Automated Attendance Sheet [PART 2]

Welcome to Part 2 of creating a fully automated attendance sheet in Microsoft Excel!

Before we begin, we recommend you to read Part 1 of creating a fully automated attendance sheet in which we have talked about creating an attendance sheet from scratch.

Read here: Creating a Fully Automated Attendance Sheet PART 1

In the second part, we will elucidate on advanced topics like highlighting Sundays, restricting data on Sundays, and counting present and absent days of every individual within a moment!

Highlighting Sundays in the automated attendance sheet

Let’s get started with highlighting Sundays in a sheet. Follow the steps carefully here.

Click on the blank cell below the first-weekday.
Go to the Home tab.
Open Conditional Formatting and press New Rule.
Choose Use a formula to determine which cells to format from the options.
Click in the Format values where this formula is true box, and click on the first weekday cell that is Thu here. Then type =”Sun”.
Now, remove the first dollar sign in the formula.

Now, press Format below.
In the Fill tab, select the cell color for all Sundays in the week.
In the Font tab, select the font colors of cells with Sundays.
Click OK when you’re done.

You won’t see anything change because the formatting has been applied to the blank cell below the first weekday.

Click on that cell and choose the Format Painter tool in the Home tab, and apply the formatting to the entire attendance sheet.

You can see that all Sundays have been highlighted in the sheet. Change months and check if Sundays in every month are highlighting correctly.

Restricting data entry on Sunday

The next step is to restrict data entry on Sundays. Follow the steps below with close attention.

Click on the blank cell below the first weekday again.
Go to the Data tab.
Click Data Validation.
In the Settings tab.
Choose Custom from Allow.
In Formula, select the first weekday of the month and type <>”Sun”.
Now, add a dollar sign before the row number in the cell address in the formula.

This is how the formula looks like.

Now, go to the Error Alert tab.
Enter a custom error title in Title. This message will be displayed every time someone tries to enter data on Sundays.
Give a custom message in the Error message section.
Click OK.

The formatting has been applied to the blank cell below the first weekday.

Apply formatting the cell to the right till the end of the table, and then to the bottom of the table from that cell.

Try entering data on a Sunday and check if you can see the error message. Now, you can enter data on regular days but not on Sundays.

To extend the attendance sheet, select an area with formatting, drag the cells by holding the bottom right corner until you see a plus sign, as long as you wish to extend the table.

Counting present and absent days

We’re very close to the end!

Now, create the Present and Absent headings to count the total presents and total absents of an individual under them.

The ultimate goal here is to automatically count the present and absent days of an individual in a month. Let’s get going!

First, mark “P” for present and “A” for absent for every individual.
To count the total number of “P” and “A”, use the COUNTIF formula.
Type =COUNTIF( in the Present column.
Select the ranges marked with P and A for the first person only.
Put a comma and type “P”.

close the brackets and press ENTER.
Repeat the steps in the Absent column by replacing the criteria with “A”

You can see that the COUNTIF formula counted present and absent the first person correctly.

Drag or double-click on the bottom right corner of the first cell to count present and absent days for other people.

The COUNTIF formula has counted the attendance for September. To record the attendance for a new month, make a copy of the sheet and start marking the attendance.

Select the area with P and A, and press Delete on your keyboard to clear texts.

Note– Before entering attendance data, save the file as a template to use it as a template in the future to create attendance sheets.

Saving the file as a template

To save the file as a template.

Go to the File tab.
Click Save As.
Click Browse.
Choose Excel Template in Save As type.

Your file will save as a template, and you can access it while creating a new Excel file.

Conclusion

This tutorial was an advanced part 2 guide to creating a fully automated attendance sheet. Part 1 elucidates on creating an automated turnout sheet from scratch. Read part 1 first, and you will understand the advanced automation settings!