How to Make Employee Attendance Statistics with esCalc

Step-by-Step Guide

1. 1

   Step 1: Suppose

   The work attendance will be calculated according to the time employee punches his/her time card.
   
   Check image below:
2. 2

   Step 2: the organization sets a rather flexible work attendance checking system: 9:00:00~18:00 in the normal working hours with lunch break 12:00~13:00.

   Method: it will be regraded as not complete the hours required of them if the office hours of a certain employee are less then 9 hours. , Firstly, group the data by employee because statistics are to be collected per employee.  , Enter formula in D3: =round(interval@s(B3,C3)/3600,2) ,, Enter formula: ={D3}.count(~<9) , For example, add backcolor for layer "1".
   
   Just select one row of layer 1, choose the backcolor you want at the right side. , Click the layer
   1.  , Use the Join function to copy data from another table to the current table according to the value in the master cell.
   
   Open the employee table in esCalc, set the employee ID as Master Cell. ,, Choose a target cell to put the joined info.
   
   Then right click on it, perform join.
   
   Get this. ,,
3. 3

   Step 3: Collect the employee absences and overtime.

4. 4

   Step 4: If the office hours of a certain employee are greater than 10 hours

5. 5

   Step 5: then the extra hours will be counted as overtime.

6. 6

   Step 6: Append two columns to calculate the short time and over time.

7. 7

   Step 7: Enter formula in E3: =if(D3>=10

8. 8

   Step 8: 0)Here

9. 9

   Step 9: finished the basic statistics.

10. 10

    Step 10: Secondly

11. 11

    Step 11: collect the statistics on the absence days per employee.

12. 12

    Step 12: Make it more friendly.

13. 13

    Step 13: Hide detailed data and only show the group level.

14. 14

    Step 14: Match the employee name in the table.

15. 15

    Step 15: Copy a name of employee by press ctrl+c

16. 16

    Step 16: And switch to the attendance sheet

17. 17

    Step 17: the ID column is already set as master cell automatically when grouping at first.

18. 18

    Step 18: Then modify the table header

19. 19

    Step 19: and delete the columns to make it an attendance sheet you want.

20. 20

    Step 20: Just copy the data to excel or txt for other use.

Detailed Guide

About the Author