6 Best Excel Formula to Calculate Overtime Over 8 Hours

Calculating overtime is essential for company payroll or time-tracking purposes. In many jurisdictions, labor laws mandate that employees receive overtime pay when they work more than a certain number of hours in a day or week, typically beyond 8 hours in a day.

This Excel tutorial will show you how to calculate overtime over 8 hours.

Consider an employee timesheet containing starting and ending times. This dataset will be used to demonstrate six formulas for each employee.

Using Simple Arithmetic Formula

Start with the basic formula to calculate overtime over 8 hours: (end time – start time) [in hours] – 8

By using a simple arithmetic formula in Excel, you can calculate overtime over 8 hours. But it returns decimals when you select Number format as the data type.

To determine the total working hours, follow the steps:

1. Select a blank cell and use the formula:
   =(E4-D4)*24
2. Drag down the Fill Handle tool to copy the formula.
   If you look carefully, the data for start and end times is in Time format. Addition and subtraction, and the arithmetic conversion on these values is calculated in days. That’s why the numeric value needs to be multiplied by 24 to get the amount of hours.
   
3. To convert into Number format, select your data, then select Number format from the Number group.
   
4. To calculate overtime, go to the G column, select the Number format for the data cells, and insert the formula:
   =F4-8
5. Finally, drag down the Fill Handle tool to copy the formula.
   

The final result is that values in the G column are the number of hours (in decimals) of overtime.

Using Excel IF Formula

One downside of the basic arithmetic method is that the formula will return negative values if a person works fewer hours, which might look odd. The IF function allows you to calculate extra time after a certain period and create branching logic. Keep in mind that you must select the Number format to get the hours in decimal.

To determine overtime with the Excel IF function:

1. Select cell F4.
2. Insert the formula:
   =IF(((E4-D4)*24) >8,((E4-D4)*24)-8,"0")
3. Use the Fill Handle to copy the formula down to the other cells in the F column.
   

With this IF formula, the output will be the same as the arithmetic method if there is overtime to calculate, but the result will be 0 otherwise.

Applying the TIME Function

The TIME function is a built-in Excel function categorized as a conversion function that returns the decimal number for a particular time. It can be used to perform arithmetic manipulation on time values or concatenate different values, but it can also be useful when calculating overtime. Follow these two basic  procedures:

Step 1: Determine Total Working Hours

First, you need to find the hours worked by the employee:

1. Select a blank cell F11 and use the formula:
   =E11-D11
2. Drag the Fill Handle up to copy the formula to the rest of the column.
   

Therefore, the total working hours for each employee are obtained.

Step 2: Calculate Overtime

Now, you need to utilize the TIME function to figure out overtime over 8 hours. Here is how:

1. Use the blank cell and insert the formula:
   =F11-TIME(8,0,0)
2. Drag down the Fill Handle tool.
   

As a result, overtime is calculated from the total working hours.

Note: The values need to be in the [h]:mm:ss format for these calculations to work properly. You can fix the format using the Format Cells option (the keyboard shortcut is Ctrl + 1).

Combining TIME & IF Functions

You can also use the combination of IF and TIME functions to calculate overtime in Excel. The IF function allows logical statements.

For example, the IF statement can be useful if you want to count extra hours worked as overtime only if it exceeds 1 hour.

To calculate this conditional overtime with the Excel IF-TIME formula:

1. Format the F column the same way as in the instructions just for the TIME function above.
2. Select a blank cell in the next column, such as G11.
3. Apply the formula:
   =IF(F11-TIME(8,0,0)>=TIME(1,0,0),F11-TIME(8,0,0),0)
4. Use the Fill Handle tool to copy the formula to the other cells in the column.
   

If you look closely at the image below, you’ll see the output of G7 and G11 as 0. The overtime is 0:30:00 and 0:55:00 respectively, which are less than 1 hour. That’s why the conditional overtime is 0:00:00.

Using MIN Function

For calculating overtime over 8 hours, the use of the MIN function can be an excellent decision. While the MIN function functions with the h:mm:ss format used for TIME functions, it’s much more straightforward to use it with regular numbers.

Let’s find the overtime by following the three steps:

Step 1: Calculate Working Hour

While subtracting the ending time from the starting time, Excel treats the time as a portion of a day. So, you need to multiply the output by 24 to get the hours in decimal values.

To calculate working hours:

1. Select the cell E11 and insert the formula:
   =(D11-C11)*24
2. Use the Fill Handle tool to autofill the rest of the column.
   

Thus, working hours for each employee show up.

Step 2: Calculate Overtime

Finally, by subtracting the regular time from the hours worked, you will obtain overtime over 8 hours. Here’s how:

1. Select a black cell (such as F4) and insert the formula:
   =E4-MIN(8,E4)
2. Drag down the Fill Handle tool to copy the formula.
   

Therefore, you can calculate the overtime over 8 hours for each employee.

Using MAX Function

If you want to compute overtime after 8 hours, the MAX function can be another way. The function will return decimals as well. For example:

1. Select a blank cell G11 and enter the formula:
   =MAX(0,E11-F11)
2. Use the Fill Handle tool to copy the formula.
   

The MAX function returns 0 if the output of subtraction is 0. Otherwise, it returns the overtime in decimal hours. For the example sheet shown for this step, the MIN function has been used to cut the hour norm for people working for fewer than eight hours, resulting in a different overtime requirement (which is still at most eight hours).

Download Practice Workbook

Conclusion

When working with time-based values, make sure to carefully consider what cell format you’re using. The Time format can be useful in setting the start and end dates, but you’ll need the custom [hh]:mm:ss format to display durations of time. For easy calculations, it can be best to use the basic Number format and manipulate the result to align with the number of hours.

Frequently Asked Questions

<< Go Back to Overtime | Formula List | Learn Excel