Excel Formula for Overtime over 8 Hours (4 Examples)

Frequently, you have to calculate overtime to track the regular and extra working time of your employees. Luckily, you can compute the overtime in Excel quickly. In this article, I’ll present to you 4 methods which mainly contain Excel formulas to calculate overtime over 8 hours.

Let’s be introduced with the following daily employee timesheet where the Starting and Ending Time are given along with other necessary information. Now we have to find overtime over 8 hours/day.

Before going to the methods, I would like to inform you that the first two methods treat overtime in h:mm format. And the rest methods calculate the overtime in decimal hours.

1. Applying the TIME Function to Find Overtime over 8 Hours in Excel

At the outset, you’ll see how we can calculate hours worked and overtime using an Excel formula containing the TIME function. The TIME function is a built-in Date and Time function in Excel that returns a decimal number of a particular time. More importantly, it is helpful if you want to use the function inside another formula.

However, we have to follow two simple steps to get overtime.

Step 1:

First of all, you need to find the hours worked by the employee. To determine this, just use the following formula.

Here, E11 is the starting cell of the ending time and D11 is the starting cell of the starting time.

Step 2:

Now, you need to utilize the TIME function as shown in the below formula.

Here. F11 is the value of hours worked.

In the above formula, I utilized the TIME function to assemble the overtime i.e. over 8 hours/day.

So, when I subtract the specific time from the total hours worked by any employee, I’ll get the extra hours of working from the total working hours.

Note: here, the overtime is in h:mm format. You can fix the format using the Format Cells option (the keyboard shortcut is Ctrl + 1).

Read More: Calculate Overtime over 40 Hours Using Excel Formula

2. Applying TIME & IF Functions to Get Conditional Overtime

Let’s say you want to find the conditional overtime (OT) that meets certain criteria. you can calculate the Overtime in hours using the Excel IF function with the TIME function. For example, you want to count it as overtime if it exceeds 1 hour.

In such a situation, just use the following formula.

Here, E11 is the starting cell of hours worked by the employee.

While explaining the formula, I can say that I assigned E11-TIME(8,0,0)>=TIME(1,0,0) as the logical_test argument of the IF function to fix the criteria of overtime exceeding 1 hour. Later, I utilized E11-TIME(8,0,0) syntax to get the amount of overtime if it fulfills the criteria; otherwise, it’ll return 0.

If you look closely at the below screenshot, you’ll get the output of G14 and G18 as 0. As the overtime is 0:30 and 0:55 respectively which are less than 1 hour. That’s why the conditional overtime is 0.

3. Using the MIN Function to Find Overtime over 8 Hours in Excel

Unlike the above two methods, we’ll calculate the overtime in decimal hours. Because the MIN function doesn’t work properly in h:mm format.

Let’s find the overtime by following the easiest 3 steps.

Step 1:

Initially, you have to find the hours worked by using the following formula.

Here, E11 is the starting cell of the ending time and D11 is the starting cell of the starting time.

More importantly, we need to multiply the output by 24 for getting the hours in decimal values as Excel treats the time as a portion of a day.

Step 2:

Now, we need to calculate the regular time utilizing the MIN function. So, the formula will be-

In the above formula, the MIN function returns 8 hours, if the hours worked is equal or greater than 8 else it returns the value of hours worked.

Step 3:

Finally, we have to subtract the regular time from the hours worked as shown in the below formula.

Here, E11 is the starting cell of the hours worked and the F11 is the starting cell of regular time.

Thus we can easily find overtime over 8 hours as the following picture shows.

4. Using the MAX Function

Furthermore, if you want to compute overtime after 8 hours of applying the MAX function, you may use the following formula.

Here, the MAX function returns 0 if the output of subtraction is 0. Otherwise, it returns the overtime over 8 hours in decimal hours.

Things to Remember

• Often you may get #VALUE! error in Excel while subtracting the two-time values if those are not in the right format.
• Consider the format of overtime (e.g. h:mm or decimal hours) while selecting the different methods.

Download Practice Workbook

Conclusion

In short, this is how you can calculate the overtime over 8 hours utilizing the Excel formula. Also, you may measure extra payment for overtime as well as overtime. I strongly believe that this article will articulate calculation methods. If you have any queries or suggestions, please let me know in the comments section below.

Related Articles

• Excel Formula to Calculate Overtime and Double Time
• Calculate Overtime Percentage in Excel