For a company that needs to calculate the overtime performed by its employees, we make a simple spreadsheet.
The company has various types of employees, of whom it needs to know the hours worked. In order to know the variety of workers and their hours, an auxiliary table is created, on the same sheet, which contemplates the number of hours each employee must work, by contract. In this table we have a “code for each type of employee”, a description and their working hours.
To determine the hours worked by each employee, we make another table, in which we put the date, the employee’s name, his entry time and his exit time. We then calculate the hours and minutes between the two hours. Then, we enter the “code for each type of employee”, in order to know which is their schedule by contract (defined in the auxiliary table), and by means of lookup and reference formulas, we locate the hours they must do and then we subtract those hours, from the ones they have done. The result is the overtime hours worked.
Application made with Excel. To use this application you need to have the MS Excel program. The application is made in Spanish. Note: The data inserted in the images are fictitious.
