Zahid Hasan
We often use Automated Attendance Sheets to keep records of the attendance of the employees or students. Using Excel, we can make Automated Attendance Sheets by following some simple steps. In this article, we are going to learn these easy steps to make an Automated Attendance Sheet in Excel.
Importance of an Automated Attendance Sheet in Excel
An Automated Attendance Sheet is used to keep track of the attendance of an organization’s employees or students. It is very tiresome work to maintain attendance for a long period if it is recorded manually. So, here comes the Automated Attendance Sheet to save us both the effort and time. Using an Automated Attendance Sheet is necessary for these reasons.
In this section of the article, we will discuss 8 easy steps to create an Automated Attendance Sheet in Excel. In the following dataset, we have Employee Details of a company. We will make an Automated Attendance Sheet for them using Excel.
Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
Step 01: Creating Support Sheet for Automated Attendance Sheet in Excel
The first step to creating an Automated Attendance Sheet in Excel is to create a Support Sheet that will help us later.
- Firstly, in the Month Name column, enter the name of 12 months as shown in the following picture.
- Following that, in the National Holidays column insert the list of National Holidays based on the region you are living in. Here, we have used the Federal Holidays of the USA.
- Finally, enter the name of the weekdays in the column named Weekday.
Read More: How to Create Employee Attendance Sheet with Time in Excel
Step 02: Creating Month and Year List for Attendance Sheet with Excel Data Validation
Now, we will create a Month and a Year List in a new worksheet. To do this, let’s follow the steps mentioned below.
- Firstly, select cell C4 as marked in the image given below.
- After that, go to the Data tab from the Ribbon.
- Following that, click on the Data Validation tab from the Data Tools group.
Consequently, the Data Validation dialogue box will open.
- Now, from the Data Validation dialogue box, click on the drop-down icon as marked in the following image.
- Then, select List from the drop-down.
- Afterward, to select the source of our data click on the marked portion of the following screenshot.
- Subsequently, select all the months under the Month Name column of the Support Sheet.
- Finally, select OK.
As a result, a drop-down icon will be available beside cell C4 like the image given below.
By using this drop-down button you can select any month according to your choice. In this case, we have selected the month of July.
- Now, click on cell C5.
- After that, go to the Data tab from the Ribbon.
- Following that, click on the Data Validation tab from the Data Tools group.
- Subsequently, choose List from the drop-down menu.
- After that, in the Source box, write the name of the years as shown in the following picture.
- Finally, click on OK.
At this stage, you will see a drop-down button available beside cell C5.
Consequently, you can choose any year from the available options by using the drop-down button. Here, we chose the year 2022.
At this stage, we will create the start date and end date of each month by using the DATEVALUE function and EOMONTH function.
- Firstly, in cell C7 enter the following formula to obtain the first date of the selected month.
=DATEVALUE("1"&C4&C5)Here, cell C4 indicates the chosen Month and cell C5 represents the selected Year.
- Then, press ENTER.
Subsequently, you will get the first day of the selected month as shown in the following image.
- Now, in cell E7, insert the following formula to get the last date of the selected month.
=DATEVALUE("1"&C4&C5)Here, C7 represents the start date of the month, and 0 means that the function will return the last date of the current month.
- Following that, hit ENTER.
Consequently, you will get the end date of the selected month. In this case, it is 31 July (31 Jul).
Read More: How to Create a Monthly Staff Attendance Sheet in Excel
Step 03: Assigning Weekends in the Automated Attendance Sheet in Excel
In this step, we will assign Weekly Off Days. Weekly off days varies from organization to organization. But the most common weekly off days is Saturday and Sunday. However, you can choose any day according to your convenience by following the steps mentioned below.
- Firstly, select cell C9 as marked in the image given below.
- After that, go to the Data tab from the Ribbon.
- Next, select the Data Validation option.
- Following that, from the Data Validation dialogue box, choose List from the drop-down menu.
- Afterward, click on the marked region to select the Source.
- Subsequently, select the weekdays under the Weekday column from the Support Sheet worksheet.
- After that, click on the marked portion of the following image.
- Lastly, click on OK.
- At this stage, a drop-down button will be available beside cell C9, and click on that.
- After that choose your weekly off day. In this case, we have chosen Saturday (Sat).
Consequently, you will see Saturday (Sat) is selected as a weekly off day as shown in the following picture.
By following the same steps, select your other weekly off day, Here, we have chosen Sunday (Sun) as our 2nd weekly off day.
Read More: How to Make Daily Attendance Sheet in Excel (2 Effective Ways)
Step 04: Entering Dates and Weekdays to the Attendance Sheet
The next step to create an Automated Attendance Sheet in Excel is to insert the days and respective weekdays of the selected month into our worksheet. Let’s follow the steps discussed below to do this.
- Firstly, in cell F11 enter the following formula.
=C7- After that, hit ENTER.
Whoa! What is this weird number? Don’t worry. We just need to change the cell format.
To change the cell format, we will the steps that are given below.
- Firstly, right-click on cell F11.
- Following that, select the Format Cells option.
As a result, the Format Cells dialogue box will open as shown in the following image.
- Next, click on the Custom option from the Format Cells dialogue box.
- Afterward, type dd in the Type box as marked in the image given below.
- Then, click OK.
Consequently, you will see the following output on your worksheet.
To obtain the rest of the days of the selected month we will use the IF function. The logic is if the cell value is less than the end date of the month then increase the cell value by 1. This means increasing cell value by 1 day. But when this condition will not be true, then replace the following cell with a blank.
- Now, we can use the following formula in cell G11.
=IF(F11<$E$7,F11+1,"")Here, F11 is the 1st date of the month. $E$7 is the end date of the selected month.
Note: We have used Absolute Cell Referencing for cell E7 ($E$7). Because we need to compare each cell with this same cell.
- Consequently, by dragging the Fill Handle, we will get the rest of the days of the selected month.
At this stage, we will enter the names of the weekdays. Here will use the TEXT function to add the names of the weekdays. Let’s follow the steps mentioned below.
- Firstly, enter the formula given below in cell F12.
=TEXT(F11,"ddd")Here, ddd is the format of the date. This means the output will contain the first 3 letters of weekdays.
- Next, press ENTER.
- Following that, use the Fill Handle to obtain the rest of the data as shown in the image given below.
Read More: How to Track Attendance in Excel (with Detailed Steps)
Step 05: Using Conditional Formatting to Highlight Weekends
Now, we will use the Conditional Formatting feature of Excel to mark both the National Holidays and the Weekly Off Days by following the steps mentioned below.
- Firstly, select cell F13 as marked in the image given below.
- After that, select the Conditional Formatting option.
- Then, choose New Rule from the drop-down.
Consequently, the New Formatting Rule dialogue box will open as shown in the following picture.
- Following that, select the Use a formula to determine which cells to format option.
- Next, insert the following formula in the Format values where this formula is true box.
=F$12=$D$9Here, $D$9 represents a Weekly Off Day (Sunday).
- After that, click on the Format option and select your preferred formatting options.
- Finally, click on OK.
Note: Here we have used Absolute Cell Referencing for cell D9 ($D$9), but for cell F12 we only fixed the Row Number. Because this will allow us to copy the formatting for all cells across the same row.
- Now, select the newly created rule.
- Following that, click on Duplicate Rule as marked in the following image.
- After that, select the duplicated rule and then click on Edit Rule.
- Then, enter the following formula in the Format values where this formula is true box.
=F$12=$C$9Here. $C$9 indicates another Weekly Off Day (Saturday).
- Afterward, click on OK.
- Now, click on Apply from the Conditional Formatting Rules Manager dialogue box.
- Next, click on OK.
At this stage, follow the previously mentioned steps to open the New Formatting Rule dialogue box as shown in the image given below.
- Following that, select the Use a formula to determine which cells to format option.
- Next, insert the following formula in the Format values where this formula is true box.
=MATCH(F$11,'Support Sheet'!$C$5:$C$15,0)- After that, click on the Format option and select your preferred formatting options.
- Finally, click on OK.
Note: Here, we have used the MATCH function to highlight the National Holidays throughout the year.
- Subsequently, select Apply.
- Then, click on OK.
- At this stage, select cell F13 as marked in the following picture.
- After that, click on the Format Painter icon.
- Following that, select the cells F13:AJ17 to paste the copied formatting in the cells F13:AJ17.
- Consequently, you will see that the Weekly Off Days and the National Holidays are marked according to your specified formatting.
Read More: How to Create Monthly Attendance Sheet in Excel with Formula
Step 06: Entering Attendance Data in the Automated Attendance Sheet in Excel
In this step, we will create a drop-down option for inserting attendance data that is present (P) or absent (A). To do this, we will follow the steps mentioned below.
- Firstly, select the cells F13:AJ17.
- After that, go to the Data tab from the Ribbon.
- Subsequently, click on the Data Validation option.
- Following that, from the Data Validation dialogue box, choose List from the drop-down.
- Then, in the Source box type P,A.
- Now, click OK.
- At this stage, click on the drop-down button beside cell F13.
- Choose P if the employee was present on that day. On the other hand, choose A if the employee was absent on that day.
- Similarly, for the other cells input the relevant attendance records or respective employees.
Read More: Attendance Sheet with Salary in Excel Format (with Easy Steps)
Step 07: Using Excel COUNTIF Function to Automatically Count Attendance
At this stage, we will use the COUNTIF function to calculate the number of Total Present and the Total Absent days. To do this, let’s use the following steps.
- Firstly, enter the following formula in cell AK13.
=COUNTIF(F13:AJ13,"P")Here, F13:AJ13 is the range of the sum and P is the sum criteria.
- Following that, hit ENTER.
- Subsequently, by dragging the Fill Handle, we will obtain the rest of the data as marked in the following image.
- Now, insert the following formula in cell AL13.
=COUNTIF(F13:AJ13,"A")Here, A is the sum criteria.
- After that, press ENTER.
- Consequently, drag the Fill Handle and you will get the following output as marked in the image given below.
Read More: How to Create Student Attendance Sheet in Excel with Formula
Step 08: Saving Automated Attendance Sheet as Template
Now, we will save the workbook as Template. As a result, we will be able to use this template in the future without going through all the above-mentioned steps. Let’s use the following steps to do this.
- Firstly, go to the Files tab from the Ribbon.
- Following that, select the Save As option as marked in the following screenshot.
- After that, rename the file according to your convenience.
- Then, choose the Excel Template (*.xltx) from the drop-down list.
- Finally, click on Save.
- To check the newly saved template, click on the New option as shown in the following picture.
- Click on the Personal tab.
There you go! You can now see your saved template. You can simply click on it and start using it.
💡 Things to Remember
- While saving the file as Template, don’t use a customized destination to save. Just use the default path suggested by Excel. This will automatically save the template to the Custom Office Template folder.
Download Practice Workbook
Conclusion
Finally, we have to the end of the article. I sincerely hope that this article was able to guide you to make automated attendance sheet in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!
Hi, This really looks helpful. However, I need help on determining shift for each employee rather declaring same week off for all employees. For Eg. Employee A will work on Weekends (Sat,Sun) and avail week off on other two days in the week. Please help me on the same. Thanks.
Hi VIJAY,
Thank you for sharing your problem with us. As you wanted to determine each employee’s shift assuming that, their holiday can be any weekday, we have modified the attendance sheet. We hope this solution will meet your requirement.
1. First, you have to create a new table that will include the preferred work shifts of each employee.
2. For simplicity, we are going to format these cells based on text value.
3. So, select the data range F10:L14.
4. Now, from the Home tab go to Conditional Formatting and Select Format only cells that contain.
5. Then you have to set Yes as Specific Text.
6. Also, choose green as the format color.
7. Then, press the OK button.
8. Similarly, create another condition that if the cell contains the specific text No, the cell will be formatted as red.
9. Your selected cells should still be blank.
10. Now, to indicate the work shift, write Yes or No in these formatted cells.
11. And, you can see the cells are instantaneously formatted with green and red colors (You can also use Yes or No from the drop-down box instead of writing them manually).
11. Now, go to the attendance sheet and suppose, there is no condition in this Shift table.
12. Select the F19 cell and create a new condition.
13. You have to select Use a formula to determine which cells to format as the New Formatting Rule.
14. Then, write the following formula in the formula box.
=INDEX($B$9:$L$14, MATCH($B19,$B$9:$B$14,0),MATCH(F$18,$B$9:$L$9,0))="No"15. Also, select a reddish format color and press OK.
16. Then, use the Fill Handle to copy the conditional formatting for all cells as shown in the following image.
17. You can see, the Attendance table has been marked by each employee’s preferred off days.
18. Now, you can select Present (P) or Absent (A) for each employee.
You may need to modify the conditional format formula according to your data table. Be careful about relative and absolute referencing. You can also check the Excel file from the link below:
Answer.xlsx
We hope this comment has been useful to you. Have a nice day.
Regards
Sourav
Exceldemy.