The attendance sheet records our attendance records such as our usual commuting, being late, leaving early, absenteeism, and asking for leave, and is also the proof of our salary. Today, the author will share with you guys how to use Excel to make a powerful attendance table, which can not only record attendance, but also automatically count attendance. Ok, let’s get started.

Step 1:

We first enter the year and month of attendance in cell A1, and then add basic information such as name, week, and date. Enter the formula in cell C3: =A1. Enter the formula in cell D3: =C3+1. After the input is complete, select cell D3 and fill to the AG cell to the right.

Step 2：

Enter the formula =C3 in C2 and Fill right

Step 3:

Place your cursor at C3 and press shortcut [Ctrl +Shift + → ] to quickly select all the right cells and right-click to choose Format Cells.

Enter “d” in the type box and click on Ok.

Do the same to the second row. Remember to enter “aaa” in the type box.

Step 4:

Now, let’s beauty our table, like fill color, add a border or adjust the size of the text.

At this time, the attendance table can automatically update the date, but the extra days cannot be automatically hidden when the number of days in a small month. As shown in the figure below:

Let’s selected the last three cells of line week and date, and go to Conditional Formatting in the toolbar and choose New Formatting Rule.

Select the last choice and enter this text: =MONTH(AE2)>MONTH(AB2)  in box of Edit the Rule Description, and click on the Format Cells button.

Select the Custom in the category and enter “;;;” in the type.

After setting, let’s change the month again. If the number of “months and days” is less, the content of the following cells will be automatically hidden.

Step 5:

Combine cells at the back to add attendance information: Attendance; Late; Leave early; Sick leave; personal; Absenteeism.

Let’s use some special symbols √；●；※；△；Ο；×

to represent these.

Step 6:

Select the date cells and go to data in the toolbar and choose Data Validation.

Choose List and select Source.

If you want to count the monthly attendance or lateness… statistics, you can use the COUNTIF function.

Step 7:

Enter these formulas in each cell.

Attendance: =COUNTIF(C4:AG4,”√”)+AI4+AJ4

Late: =COUNTIF(C4:AG4,”●”)

Leave Early: =COUNTIF(C4:AG4,”※”)

Sick Leave: =COUNTIF(C4:AG4,”△”)

Personal Leave: =COUNTIF(C4:AG4,”Ο”)

Absenteeism: =COUNTIF(C4:AG4,”×”)

Now, you have a perfect attendance sheet.