Maruf Islam
Excel is a useful tool to perform any type of calculation. Therefore, users try to calculate overtime hours in Excel using the IF function. The IF function has its own syntax as IF (logical_test, [value_if_true], [value_if_false]).
Let’s say we have a dataset that contains employees’ total working time in hours and minutes. Now, we want to find the overtime hours (i.e., only hours or hours and minutes) using the IF function.
This article demonstrates multiple variants of the IF functions to calculate overtime hours in Excel.
Download Excel Workbook
4 Effective Examples to Calculate Overtime Hours in Excel Using IF Function
From the syntax, we know that the IF function takes a logical_test in its argument and performs the assigned operations depending on the outcomes of the test (i.e., TRUE or FALSE). Go through the following examples to get familiar yourself with multiple variants of the IF function.
Examples 1: Overtime Calculation Using IF and TIME Function with Custom Text
🔄 If users need to calculate overtime hours (i.e., hours and minutes) and want to place a Custom Text (i.e., No Overtime) where no overtime occurred. Use the following formula in any adjacent cell (i.e., F6).
=IF(E6-TIME(8,0,0)>0,E6-TIME(8,0,0),"No Overtime")In the formula, the IF function takes E6-TIME(8,0,0)>0 as its logical test. The test finds that E6-TIME(8,0,0) amounts to more than zero (0). Where the TIME function deducts the 8 hours from the time worked amounts. If the test results in TRUE, it returns the overtime; otherwise “No Overtime” text.
🔺 Press ENTER then drag the Fill Handle to find the overtime hours and minutes.
Read More: How to Calculate Hours Worked and Overtime Using Excel Formula
Examples 2: Calculating Overtime Using IF and TIME Functions with Condition
🔄 Now, what if users want to consider overtime hours only greater than 1 as overtime hours. In that case, the previous formula needs a slight upgrade. Instead of the previous IF and TIME formula, use the below formula to calculate only the overtime hours of more than 1.
=IF(E7-TIME(8,0,0)>=TIME(1,0,0),E7-TIME(8,0,0),0)The logical_test gets a slightly upgraded version of E7-TIME(8,0,0)>=TIME(1,0,0). Where the TIME function ensures only the overtime hours more than 1 are considered as overtime.
🔺 Hit the ENTER key, then drag the Fill Handle to apply the formula to other cells as shown in the below picture.
Read More: Excel Formula to Calculate Overtime and Double Time (3 Ways)
Examples 3: IF Function with Direct Cell Reference in Overtime Calculation
🔄 Sometimes, users have regular working hours in a cell. And they can use this cell as a direct reference in the overtime calculation. As you can see, in the dataset we have the normal working hours in cell C4. We use that cell to insert the logical_test and calculate the overtime. Type the below formula in cell F7.
=IF(E7>$C$4,E7-$C$4,"N/A") 
🔺 After the formula insertion, drag the Fill Handle to find the overtime hours (i.e., hours and minutes) in other cells.
Read More: How to Calculate Overtime Percentage in Excel (3 Quick Methods)
Examples 4: IF Function without Cell Formatting in Overtime Calculation
🔄 In some cases, users don’t apply formatting or the cell values don’t have specific formatting.
In those cases, users need to find the General (Cell Format) value of 8 hours (i.e., 0.33) and use it in direct cell references to insert logical_tests or cell references.
=IF(E7>$C$4, (E7-$C$4)*24,0)Multiplying time values (that are in General Format) by 24 results in hours.
🔺 Use the ENTER key to apply the formula. Then drag the Fill Handle to apply the formula in other cells as depicted in the picture below.
Read More: Excel Formula for Overtime over 8 Hours (4 Examples)
Conclusion
In this article, we demonstrate multiple variants of the IF function to calculate overtime hours in Excel using the IF function. We discuss the outcomes as well as cell formats in calculating overtime hours. We hope these above examples clarify your understanding regarding the calculation of overtime hours. Comment if you have further inquiries or have anything to add.
