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”**.

** **

**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.

** **

**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**

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 WILLIETSThanks 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, andTotal Pay. Assuming you get$76.5for overtime hours and$51for 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 HoursSelect cell

E3=> Insert the following formula => Drag theFill Handleicon to cellE10.Step 2: Overtime HoursSelect cell

F3=> Insert the formula below => Drag theFill Handleicon to cellF10.Step 3: Overtime PayChoose cell

G3=> Insert the formula below => Drag theFill Handleicon to cellG10.Step 4: Normal Hours PayChoose cell

H3=> Insert the formula below => Drag theFill Handleicon to cellH10.Step 5: Total PayChoose cell

I3=> Insert the formula below => Drag theFill Handleicon to cellI10.I am also attaching the solution workbook for better understanding. Hopefully, the idea will help you to reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy

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