How To Create Attendance Sheet In Excel

Date:2021-3-1 Author:Sandra

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.

How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel

Step 2:

Enter the formula =C3 in C2 and Fill right

How To Create Attendance Sheet In Excel

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.

How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel

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

How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel

Step 4:

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

How To Create Attendance Sheet In Excel

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:

How To Create Attendance Sheet In Excel

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.

How To Create Attendance Sheet In Excel

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

How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel

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.

How To Create Attendance Sheet In Excel

Step 6:

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

How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel

Choose List and select Source.

How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel
How To Create Attendance Sheet In Excel

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.

How To Create Attendance Sheet In Excel

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.

How To Create Attendance Sheet In Excel

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

One response to “How To Create Attendance Sheet In Excel”

  1. MUSA USMAN ADAMU says:

    VERY USEFUL AND A BIT DIFFICULT TO UNDER STAND

Leave a Reply

Your email address will not be published. Required fields are marked *