Md. Abdul Kader
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.
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.
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.
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.
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.
Read More: Excel Formula To Calculate Time Worked
Similar Readings
- [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
- How to Subtract Time in Excel (7 Quick Methods)
- Calculate Total Hours in Excel (9 Easy Methods)
- How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)
- Calculate Turnaround Time in Excel (4 Ways)
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.
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.
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.
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.
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.
