separating OT hours from total hours

[Bob_ipc]

I have a column H13:H each cell in the column represents the total hours worked that day, I am trying to come up with a formula where 9 hours of every day will be regular hours and all hours after 9 will be multiplied by X1.5. I currently have

Total=396 hours
Regular=258
OT=138 x1.5 =207

So Grande total with OT should equal 465

I currently have come up with =SUBTOTAL(9,H13:H640)+(SUMPRODUCT(SUBTOTAL(3,OFFSET(H13:H640,ROW(H13:H640)-MIN(ROW(H13:H640)),,1)),--(H13:H640>9),H13:H640))*0.5
But my result is too high 559.

Any ideas? It would be appreciated thank you.

 

[FDibbins]

I would probably add a helper column and run the calc for each row - then sum the helper. This will keep it simple

 

[jim may]

Maybe simply...


Excel 2010

	G	H	I	J	K

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Tot Hours[/TD]
[TD="align: center"]Reg Hours[/TD]
[TD="align: center"]OT Hours[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I13[/TH]
[TD="align: left"]=MIN(H13,9)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J13[/TH]
[TD="align: left"]=H13-I13[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H20[/TH]
[TD="align: left"]=SUM(H13:H19)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I20[/TH]
[TD="align: left"]=SUM(I13:I19)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J20[/TH]
[TD="align: left"]=SUM(J13:J19)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Bob_ipc]

Could you give me an example? This page is part of our payroll summary so if I could I would like to stay away from a helper column to make it look professional.

Thank you for you quick reply

 

[FDibbins]

Maybe something like this?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Using real time[/td][td][/td][td][/td][td]Using whole numbers[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

5:00​

[/td][td]

5:00:00​

[/td][td][/td][td]

5​

[/td][td]

5​

[/td][/tr]

[tr][td]

3​

[/td][td]

8:00​

[/td][td]

8:00:00​

[/td][td][/td][td]

8​

[/td][td]

8​

[/td][/tr]

[tr][td]

4​

[/td][td]

11:00​

[/td][td]

12:00:00​

[/td][td][/td][td]

11​

[/td][td]

12​

[/td][/tr]

[tr][td]

5​

[/td][td]

12:00​

[/td][td]

13:30:00​

[/td][td][/td][td]

12​

[/td][td]

13.5​

[/td][/tr]

[tr][td]

6​

[/td][td]

17:00​

[/td][td]

21:00:00​

[/td][td][/td][td]

17​

[/td][td]

21​

[/td][/tr]

[tr][td]

7​

[/td][td]

18:00​

[/td][td]

22:30:00​

[/td][td][/td][td]

18​

[/td][td]

22.5​

[/td][/tr]

[tr][td]

8​

[/td][td]

23:00​

[/td][td]

30:00:00​

[/td][td][/td][td]

23​

[/td][td]

30​

[/td][/tr]
[/table]
B2=IF(A2<=9/24,A2,(A2-9/24)*1.5+9/24)
OR
E2=IF(D2<=9,D2,(D2-9)*1.5+9)
copied down

As far as keeping up appearances, you can hide the helper so it doesn't show

 

[Bob_ipc]

Hey thanks Jim that might work for what I have, but could get your OT hours in column I and not have column J?

 

[jim may]

Just change to:

Cell I13 formula to: =H13-MIN (H13,9)

 

[Bob_ipc]

Gentlemen that has been huge thank you so much! It must be Monday that should not have been that difficult haha.
Thank you Both!

 

[FDibbins]

Happy to help, keep me posted

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1			Sum	73	61	12
2	# > 9	3		Total	Regular	OT
3	Regular	61	61	8	8	0
4	OT	12	18	9	9	0
5				8	8	0
6				9	9	0
7				12	9	3
8				12	9	3
9				15	9	6
2a

Cell Formulas
Range	Formula
E3	=MIN(D3,9)
F3	=D3-E3
B2	=COUNTIF(D3:D9,">9")
B3	=SUMIF(D3:D9,"<=9",D3:D9)+B2*9
B4	=SUMIF(D3:D9,">9",D3:D9)-B2*9
C4	=B4*1.5