1, fill in the date
A. Enter the date in the cell and add information such as name, week and date.
B, C3 cell input: =A 1, D3 input: = C3+1;
C, select D3 to start filling the AG cell to the right.
Step 2 fill in the week
Enter: =C3 in the C2 cell and fill it to the right.
3, set the cell format
A, select C3 cell, Ctrl+shift+→ select the back cell, open and set the cell format, and enter D in Custom;
B, also choose a line on Monday, and set the custom format to aaa.
Step 4 beautify the attendance sheet
Fill the table with colors, add borders, center alignment, etc. Adjust the table information, enter the time sheet and other words, and set the cell adaptive table size.
5. The date can be updated automatically.
A, modify the cell month date can be automatically updated;
B. If there are fewer days after the update, the extra days will be displayed later.
Step 6 hide the extra days
A, select the last three cells and enter the conditional format-create a new rule-determine the cells to be formatted with a formula -Enter =MONTH(AE2) > month (ab2);
B, input format-number-user-defined, modified as: ; .
7. Add attendance symbols
A. Merge two rows of cells and enter attendance contents, such as attendance, leaving early, being late, etc.
B, in the corresponding upper input attendance symbol, attendance = √, late = ○, leave early =□, sick leave = ◇, personal leave = 10, absenteeism =×.
8. Data validation
Select all the cells that need to fill in attendance, and click Data-Data Validity-Settings to select the order in the window, and select the cell where the attendance symbol is located in the formula.
9. Enter the attendance statistics formula.
Attendance = countif (C4: ag4, "√")+AI4+AJ4;
Late = countif (C4: Ag4，“○”)；
Leave early = countif (C4: Ag4, "□");
And so on =COUNTIF(C4:AG4, "corresponding symbol"), just modify the symbol.
10, filling formula
Select the cell for which the formula has been set and drop it down to fill it. When we set up attendance, we will automatically count the attendance days.
The above is a tutorial for Excel to make time sheets. Pick it up and make it yourself!
That's what Bian Xiao shared about how to make an attendance sheet with Excel. For more information, you can pay attention to Global Ivy and share more dry goods.