If Statements for OT hours after 40

sklnxbones

New Member
Joined
Jun 11, 2008
Messages
26
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

chrisl

Board Regular
Joined
Nov 21, 2006
Messages
58
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
Joined
Aug 26, 2002
Messages
722
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
Joined
Jun 11, 2008
Messages
26
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
Joined
Jun 11, 2008
Messages
26
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top