# If Statements for OT hours after 40

#### sklnxbones

##### New Member
Ok its me again...
This is what I am trying to do...
Column F is the number of hours worked per day
Column G is the Cumulative hours worked for the week
I want Column H to figure out how many Over Time Hours (hours >40) for that day. So this is my thought process... If G13 is greater than 40, then take G13 and subtract 40, and subtract any overtime from previous days so I get the over time worked per day. BUT if NO over time is worked I want nothing to appear in column H...

This is the formula I am using... that is NOT working

=IF(\$G13>"40:00",(SUM((\$G13-"40:00")-SUM(H7:H12))),"")

Can any one tell me whats wrong with this formula or give me a better one????

Any help would be greatly appreciated...

Thanks so much

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### chrisl

##### Board Regular
Not sure if I understand exactly the cell references for each of your formulae.
However if the entries are entered in time/date format, then I think you need to convert your 40 hours into days (i.e. 40/24 or 1.6667 days) for the formulae to evaluate properly.

So try replacing "40:00" with 40/24 using no quotation marks.

#### justme

##### Well-known Member
If I understood your post correctly:

Col F is hours worked per day
Col G is cummulative hours
Col H is OT calculation for 40 hour work week.

I started my test in cell F3. In G3 I have the following formula: =G2+F3. I copied this formula down column G.

In column H I typed this formula in H3 and copied down the column: =IF(G3>40,G3-40,"")

Is this what you needed?

#### sklnxbones

##### New Member
this is the problem I am having...

lets say on day 5 I hit the over time mark, >40

so in day 5 I have 5 hours of over time, so that would be 45

day 6 I work 7 hours, all of those hours would be OT,

and day 7 I work 7 hours, again all of these hours would be OT too...

because the hours in G are cumulative hours, the results I am getting are...

day 5, 5 OT hours
day 6 12 OT hours
day 7, 19 OT hours

the correct results should be...

Day 5, 5 OT hours
Day 6, 7 OT hours
Day 7, 7 OT hours

the TOTAL is 19 hours... see the difference?

#### barry houdini

##### MrExcel MVP

Assuming F7 down contains the daily hours use this formula in G7 down for the cumulative hours

=SUM(F\$7:F7)

H6 should be blank or a text header then H7 copied down can be

=IF(OR(G7<=40,F7=0),"",G7-40-SUM(H\$6:H6))

#### sklnxbones

##### New Member
Barry...

I copied your formulas and the results were all cells in H were blank...

This is what I want it to be...

column f column g column h
Hours Cumulative OT Hours
9:00 9:00
6:30 15:30
7:00 22:30
8:00 30:30
7:00 37:30
8:00 45:30 5:30
7:00 52:30 7:00

The problem that I am having is with the over time hours. So far every formula I come up with does not take into account the previous days overtime.... so the results I get are this

column f column g column h
Hours Cumulative OT Hours

8:00 45:30 5:30
7:00 52:30 12:30

Because G is cumulative the last entry is 52:30, which is 12:30 greater than 40, but the result I want is 7:00

#### barry houdini

##### MrExcel MVP
OK, I think the formula was OK but I'd assumed you had hours shown in decimal format not time format. Try changing H7 formula to

=IF(OR(G7<=40/24,F7=0),"",G7-40/24-SUM(H\$6:H6))

copy down as before and custom format all cells as [h]:mm

#### sklnxbones

##### New Member
That was it....

Works PERFECTLY NOW....

Thanks so much..

Replies
1
Views
446
Replies
1
Views
416
Replies
3
Views
447
Replies
2
Views
240
Replies
1
Views
220

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,951
Messages
5,834,543
Members
430,295
Latest member
amdis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back