Attendance sheet are no strangers, you can record attendance at work, today we teach you to create a dynamic attendance sheet template, you can automatically update the date, you can also count the number of days of attendance, HR personnel must have the knowledge!

1, fill in the date

A, enter the date in the cell, add the name, day of the week, date and other information.

B, C3 cell input: = A1, D3 input: = C3 + 1;

C, select D3 to start filling to the right to AG cells.

2, fill week

In cell C2 enter: = C3, fill to the right.

3, set the cell format

A, select C3 cell, Ctrl + shift + → select the back of the cell, open set the cell format, enter d on the custom;

B, the same selection of the Monday line, set the custom format for aaa.

4, beautify the time sheet

Fill the form with colors , add borders, center alignment and so on. Adjust the form information, enter the attendance sheet and other text, set the cell to adapt to the size of the form and so on.

5, the date can be automatically updated

A, modify the cell month date can be automatically updated;

B, after the update if the number of days is relatively small, the back of the display of the number of extra days.

6, hide the extra days

A, select the back of the three cells, enter the conditional format - new rules - use the formula to determine the format of the cell - enter = MONTH (AE2) & gt; MONTH (AB2);

B, enter the format - digital - customize the type of modification to the ;;;.

7, add attendance symbols

A, merge the two rows of cells and enter the content of the attendance, such as: attendance, early departure, tardiness, etc.;

B, in the corresponding above enter the attendance symbols, attendance = √, tardiness = ○, early departure = □, sick leave = ◇, personal leave = 10, absenteeism = ×.

8, data validation

Select all the cells to fill in the attendance, click on the data - data validation - Settings, in the window, select the sequence, the formula to select the attendance symbols where the cell.

9, enter the attendance statistics formula

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

late = COUNTIF (C4:AG4, "○");

early = COUNTIF (C4:AG4, "□");

behind the similar= COUNTIF(C4:AG4, "corresponding symbol"), just change the symbol.

10, fill the formula

Select the cell set up a good formula to fill the drop-down, when we set up attendance, it will automatically count the number of days of attendance.

The above is the Excel tutorials to make attendance sheets, get up to make their own!

The above is a small number of people to share with you on how to use Excel to make attendance sheet? The relevant content, more information can be concerned about the Global Green Ivy share more dry goods