If Statements for OT hours after 40

[sklnxbones]

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

 

[chrisl]

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]

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]

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]

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]

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

Please help me

 

[barry houdini]

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]

That was it....

Works PERFECTLY NOW....

Thanks so much..