Make Employee Attendance Statistics with esCalc
Many organizations need to manage employee attendance, such as absenteeism or tardiness, overworking hours as the reference for employee performance rating. It is routine for the statistician to collect working attendance statistics at the end of each month. For organizations who have thousands of employees, statistics on working attendance performance about all employees can be troublesome. Is there a method to solve the work attendance problem efficiently? How about 20 minutes? Here is how to make employee attendance statistics based on working hours easily.
Steps
- Suppose, 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. The work attendance will be calculated according to the time employee punches his/her time card. Check image below:
- Collect the employee absences and overtime. 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.
- If the office hours of a certain employee are greater than 10 hours, then the extra hours will be counted as overtime. Firstly, group the data by employee because statistics are to be collected per employee.
- Append two columns to calculate the short time and over time. Enter formula in D3: =round(interval@s(B3,C3)/3600,2)
- Enter formula in E3: =if(D3>=10,D3-9,0)Here, finished the basic statistics.
- Secondly, collect the statistics on the absence days per employee. Enter formula: ={D3}.count(~<9)
- Make it more friendly. For example, add backcolor for layer "1". Just select one row of layer 1, choose the backcolor you want at the right side.
- Hide detailed data and only show the group level. Click the layer 1.
- Match the employee name in the table. 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.
- Copy a name of employee by press ctrl+c
- And switch to the attendance sheet, the ID column is already set as master cell automatically when grouping at first. Choose a target cell to put the joined info. Then right click on it, perform join. Get this.
- Then modify the table header, and delete the columns to make it an attendance sheet you want.
- Just copy the data to excel or txt for other use.
Tips
- There are function reference for you to refer if you don't remember it.
- Make sue the date format in esCalc beconsistent with your punch format. To change the time format, go to Tools-Option-Environment.
