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.
Example 1 – Overtime Calculation Using the IF and TIME Functions with Custom Text
- 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
- 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
- Use the formula:
=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
Hi Guys/Gals
What I cannot wrap my head around is a ‘make up time’ scenario (and also allowing additional OT).
Normal work hours are 07:00 to 15:30 (M-Th) and 07:00 to 13:00 (Fri). 38 hour week.
The timesheet portion (in ‘time’) is under control, with a summary of total hours (in ‘time’) in column H.
Beyond column H, I do a conversion to ‘digital time’, with the conversion/total for the day in column L. Everything beyond column H is ‘digital time’, so no need for any further time conversions.
So I have ‘total hours worked’ in column L. Normal hours (for pay rate effectively) in column N, and OT hours in column P. I also have a hidden reference column J, stating the normal working hours (8, 8, 8, 8, 6) for each day.
What I currently have is row calculations per day, in the N and P columns, splitting up normal hours from OT hours. This would all be fine, for a full week’s work plus OT, but the ‘flexible’ element comes in, whereby, if the employee took off time one day, they could make it up the next day/s during that week.
At the bottom of each column I have the totals. Example, hours worked (column L) comes to 38 hours. Normal hours (column N) comes to 37 (ie, under the 38) and OT hours (column P) comes to 1. Clearly this is a ‘make up’ scenario, whereby the OT needs to be counted back into Normal hours (also acknowledging there could be the scenario whereby they made up the short hours, and also did additional OT hours in that week).
Currently the OT row calculations are =MAX(0,L12-J12), and the Normal hour calcs are =L12-P12 with both columns autosummed in rows below. Leaving me (in this scenario) 38 hours worked (column L total), Normal hours (column N total) of 37 and OT hours (column P total) of 1 hour.
***So the goal is to put OT hours (column P) in with Normal hours (column N) IF column N falls below 38. But if column N is already 38, then column P total should be regarded as OT hours.***
I think I am having trouble with the IF function on this one, and how I should reference it all. And I hope I have explained it well enough (if I could have attached screenshot, would have been less words involved).
I think I need the sum of column N (normal hours) to look at column P (OT hours) and add anything there to get to the 38. If column N already has 38 hours, then column P should reflect those as OT hours. And I have final total cells below those tallies.
Hope that all makes sense. Thanks in advance for your help.
Hello Anne K,
You can modify the logic of calculating Normal hours (column N) and OT hours (column P), specifically targeting the total at the bottom of each column. If Normal hours (column N) is less than 38, then part of the OT hours (column P) should be added to reach 38. Once Normal hours reach 38, any remaining OT should stay in column P.
In the cell where you total your Normal hours (say N20), you can use this formula to adjust for the scenario where OT hours should be added to the Normal hours if the total normal hours are less than 38.
Updated Formula:
=MIN(38, SUM(N12:N19) + SUM(P12:P19))
In the cell where you total your OT hours (say P20), adjust the OT formula so that it only shows overtime that is truly above 38 hours of work.
Updated Formula:
=MAX(0, SUM(L12:L19) – 38)
This should solve the issue where overtime hours need to “make up” for missing normal hours but still allows for extra OT beyond 38 hours. Let me know if this works for you or if you need any further tweaks!
Regards
ExcelDemy