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.
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, you can calculate working hours and Overtime Using Excel Formula such as a combination of IF and TIME function. 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.
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.
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.
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.
Download Excel Workbook
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.
<< Go Back to Overtime | Formula List | Learn Excel
how do you make a personal time sheet that allows anytime before 6am and after 6pm is to be overtime,
example;
my normal hours are paid by the minute from 6am to 6pm
my shift is min 10.5 hours per day take away unpaid break of 30 mins
if i start before 6am then its overtime rates and again after 6pm is overtime rates
how do i make a simple sheet so i can track my pay
example 1
05:37 start and 15:39 finish (06:00-05:37) = 00:23mins Overtime, (15:39-06:00)=10:09 Hours Normal pay 30mins Break)
many thanks Martyn
Hello MR MARTYN WILLIETS
Thanks for reaching out and sharing your requirements. You wanted to create a personal payment tracker sheet. To create such a sheet, we can work with fields like Date, Start Time, End Time, Total Paid Hours, Overtime Hours, Overtime Pay, Normal Hours Pay, and Total Pay. Assuming you get $76.5 for overtime hours and $51 for regular hours. I am delighted to share how you can create a sheet to fulfil your goal.
Overview of Personal Payment Tracker Sheet:
Follow these steps:
Step 1: Total Paid Hours
Select cell E3 => Insert the following formula => Drag the Fill Handle icon to cell E10.
Step 2: Overtime Hours
Select cell F3 => Insert the formula below => Drag the Fill Handle icon to cell F10.
Step 3: Overtime Pay
Choose cell G3 => Insert the formula below => Drag the Fill Handle icon to cell G10.
Step 4: Normal Hours Pay
Choose cell H3 => Insert the formula below => Drag the Fill Handle icon to cell H10.
Step 5: Total Pay
Choose cell I3 => Insert the formula below => Drag the Fill Handle icon to cell I10.
I am also attaching the solution workbook for better understanding. Hopefully, the idea will help you to reach your goal. Good luck.
DOWNLOAD WORKBOOK
Regards
Lutfor Rahman Shimanto
ExcelDemy