So, the final formula to calculate “ Worked Hours ” in this example would be as follows as entered in cell D2 įigure 3. =(Finish_time - Start_time+(Finish_time < Start_time))*24 In this case, we need to make some adjustments in basic above worked hours calculation formula, such as As Excel natively handles the time as a fraction of the day, therefore we need to convert this fraction to decimal hours to make worked hours calculations simple and easy by multiplying the difference of start and finish timings by 24, such as Īnd if there is a case, where shift crosses the midnight then we also need to calculate elapsed time in total worked hours calculation. This is done by simply subtracting start time from the finish time. Setting Up Spreadsheet Calculating Worked Hoursįirst, we need to calculate the total worked hours based on employees’ shift start and finish timings.
This time tracking is important to make calculations for worked hours, regular hours and overtime hours, such as column D, E and F respectively in our example.įigure 2. Overtime Calculation in Excel Setting Up the Spreadsheetįirst of all, we need to set up our spreadsheet to track the start time and finish time of employees’ shift, such as column B and C.
This article will step through the process of calculating overtimeįigure 1. So, we can easily keep track of these times to calculate overtime by comparing the total hours worked with the regular hour threshold. The total worked hours and overtime hours calculations are dependent on employees’ start time and finish time.
0 kommentar(er)
