Excel Formula for Overtime over 8 Hours (4 Examples)

Frequently, you have to calculate overtime for tracking 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 formula to calculate overtime over 8 hours.


Download Practice Workbook


Methods of Calculating Overtime over 8 Hours Using Excel Formula

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.

Excel Formula for Overtime over 8 Hours Dataset

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 overtime using the TIME function quickly. 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. For determining this, just use the following formula.

=E11-D11

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

Applying the TIME Function to Find Overtime over 8 Hours

Step 2:

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

=F11-TIME(8,0,0)

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.

Applying the TIME Function to Find Overtime over 8 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: How to Add Time in Excel Over 24 Hours (4 ways)


2. Applying TIME & IF Functions to Get Conditional Overtime 

Let’s say you want to find the conditional overtime (OT) that meets certain criteria. For example, you want to count it as overtime if it exceeds 1 hour.

In such a situation, just use the following formula.

=IF(E11-TIME(8,0,0)>=TIME(1,0,0),E11-TIME(8,0,0),0)

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 popular IF function to fix the criteria of the 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.

Excel Formula for Overtime over 8 Hours Applying TIME & IF Functions

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.

Applying TIME & IF Functions

Read More: Excel Formula To Calculate Time Worked


Similar Readings:


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.

=(D11-C11)*24

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.

Using the MIN Function

Step 2:

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

=MIN(8,E11)

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.

Overtime over 8 Hours Using the MIN Function

Step 3:

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

=E11-F11

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 the overtime over 8 hours such as the following picture shows.

Using the MIN Function

Read More: Excel Timesheet Formula with Lunch Break (3 Examples)


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.

=MAX(0,E11-F11)

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

Using the MAX Function

Related Content: Excel Formula for Overtime over 40 Hours [with Free Template]


Things to Remember

  • Often you may get #VALUE! error 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.

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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo