Learn How to Calculate Overtime in Excel

The total worked hours and overtime hours calculations are dependent on employees’ start time and finish time. So, we can easily keep track of these times to calculate overtime by comparing the total hours worked with the regular hour threshold. This article will step through the process of calculating overtime

Figure 1. Overtime Calculation in Excel

Setting Up the Spreadsheet

First 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 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.

Figure 2. Setting Up Spreadsheet

Calculating Worked Hours

First, we need to calculate the total worked hours based on employees’ shift start and finish timings. This is done by simply subtracting start time from the finish time. 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;

=(Finish_time - Start_time)*24

And if there is a case, where shift crosses the midnight then we also need to calculate elapsed time in total worked hours calculation. In this case, we need to make some adjustments in basic above worked hours calculation formula, such as;

=(Finish_time - Start_time+(Finish_time < Start_time))*24

So, the final formula to calculate “Worked Hours” in this example would be as follows as entered in cell D2;

=(C2-B2+(C2<B2))*24

Figure 3. The formula to Calculate Worked Hours

Calculating Regular Hours

The regular hours’ calculation is essential to calculate overtime in Excel and it is done by comparing defined regular hours, say 8 hours, with worked hours calculated in column D, whichever is smaller. For this, we use the MIN function in column E to calculate “Regular Hours” by entering the following formula in cell E2;

=MIN(8,D2)

Figure 4. Calculating Regular Hours

 Calculating Overtime Hours

After calculating worked hours and regular hours, now we are able to calculate overtime in Excel. This is simply worked hours minus regular hours or applying the MAX formula in cell F2, such as;

=MAX(0,$D2-$E2)

Figure 5. Calculating Overtime Hours

Now select the cells D2, E2, and F2 and drag down this selection to apply this set of formulas to other rows in the spreadsheet as shown below;

Figure 6. Dragging the Formulas Set

Figure 7. Final Preview of Calculations

Worked Hours Calculation with Break Times in Shift

If we incorporate break timings in our spreadsheet, such as “Lunch Start” and “Lunch End” timings, then we need to adjust the worked hours formula to subtract these break hours from worked hours in order to correctly calculate overtime in Excel. So, the formula for worked hours would be as follows:

=((E2-B2+(E2<B2))-(D2-C2))*24

Figure 8. Calculating Worked Hours by Subtracting Break Hours

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar