The IF function has its own syntax:
IF (logical_test, [value_if_true], [value_if_false]).
The sample dataset contains employees’ total working time in hours and minutes. To find the overtime hours you can use use the IF function.
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).
Example 1 – Overtime Calculation Using the IF and TIME Functions with Custom Text
To calculate overtime hours, placing a Custom Text (No Overtime) you can combine the IF and TIME function. Use the following formula in any adjacent cell ( F6).
=IF(E6-TIME(8,0,0)>0,E6-TIME(8,0,0),"No Overtime")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). The TIME function deducts the 8 hours from the time worked amount. If the test is TRUE, it returns the overtime; otherwise “No Overtime”.
Press ENTER and drag the Fill Handle to find the overtime hours and minutes.
Example 2 – Calculating Overtime Using IF and TIME Functions with a Condition
Use the formula to calculate the overtime hours more than 1.
=IF(E7-TIME(8,0,0)>=TIME(1,0,0),E7-TIME(8,0,0),0)The logical_test is slightly changed E7-TIME(8,0,0)>=TIME(1,0,0). The TIME function returns overtime hours more than 1 only.
Press ENTER and drag the Fill Handle to apply the formula to the other cells.
Examples 3 – Using the IF Function with Direct Cell Reference in Overtime Calculation
A cell with regular working hours can be used as a direct reference in overtime calculation.
In the dataset, normal working hours are in C4, which is used to insert the logical_test and calculate overtime. Enter the formula in F7.
=IF(E7>$C$4,E7-$C$4,"N/A") 
Drag the Fill Handle to find overtime hours in other cells.
Example 4 – Using the IF Function without Cell Formatting in Overtime Calculation
When cell values don’t have specific formatting, you need to find the General (Cell Format) value of 8 hours (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.
Press ENTER. Drag the Fill Handle to apply the formula to the other cells.
Download Excel Workbook
<< Go Back to Overtime | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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