Attendance sheet format:How to create an attendance sheet template formula in Excel?-Font Tutorial免费ppt模版下载-道格办公

How to create an attendance sheet template formula in excel? It is very troublesome to count attendance in Excel. I want to make a template directly and apply it directly in the future. How to set it up? Let’s take a look at the detailed tutorials for the accounting education exam. Friends who need it can refer to it.

1. Introduction to common functions and formulas of attendance sheets:

First of all, the most basic thing is to calculate the time employees work that day, as shown below. Enter the formula =B2-A2 in C2 to get the length of time employees work that day (you can consider subtracting the noon break time).

2. In addition, judging whether employees are late or leave early is also a basic function of the attendance sheet. Assuming that clocking in after 08:30 in the morning is considered late, then C2=IF(A2-"08:30">0,"late","") is sufficient.

3. Assuming that you leave early before 17:30 in the evening and clock in, thenC2=IF(B2-"17:30"<0,"Leave early","").

4. As shown in the picture below, if employees work normally, mark the date with a check mark, otherwise mark it with a circle (the actual situation may be more complicated, such as sick leave, personal leave, paid annual leave, etc.). The formula can be used to calculate the number of attendance days of an employee in the current month: J4=COUNTIF(C4:I4,"√")

5. If the system swipes the card or punches in by fingerprint, the generated data may be in the following table format, with one column for the name and one column for each punch-in time. Since a person may clock in multiple times a day and may miss clocking in at work and off work, identification is also required. So we create a label auxiliary column in column C.

6. Double-click C2 and enter the formula: =IF(B2=MIN(IF(A$2:A$6=A2,B$2:B$6)),"Go to work",IF(B2=MAX(IF(A$2 :A$6=A2,B$2:B$6)),"off duty","")), hold downCtrl+Shift with your left hand,press Enter with your right hand to run the formula and press Fill in the formula below.

7. MIN(IF(A$2:A$6=A2,B$2:B$6)) returns the minimum value that “Zhang San” punched in that day;MAX(IF(A $2:A$6=A2,B$2:B$6))What is returned is the maximum value of "Zhang San"'s check-in on that day; the effect of nested IF outside is: if the time in column B is equal to "Zhang San's" check-in on that day The minimum value is recorded as "going to work"; if the time in column B is equal to the maximum value of "Zhang San"'s clocking in that day, "off duty" is recorded; the middle clocking in record is empty.

8. Select the data area, click [Insert]-[PivotTable], and create it according to the default settings.

9. Drag the three fields to the corresponding positions of the pivot table as shown in the 10th step, and organize the format as follows:

10. There are times under the "On Time" and "Off Time" fields respectively, which are the earliest and latest punch-in records of the employee that day. If the "Off Time" record is empty, it means that the employee punched in only once that day (because only one record is counted. during "office" hours).