separating OT hours from total hours

Bob_ipc

Board Regular
Joined
Oct 18, 2017
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I would probably add a helper column and run the calc for each row - then sum the helper. This will keep it simple
 
Upvote 0
Maybe simply...


Excel 2010
GHIJK
10
11
12Tot HoursReg HoursOT Hours
131091
141192
15990
16770
17660
181091
19550
2058544
21

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I13=MIN(H13,9)
J13=H13-I13
H20=SUM(H13:H19)
I20=SUM(I13:I19)
J20=SUM(J13:J19)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
Maybe something like this?
A​
B​
C​
D​
E​
1​
Using real timeUsing whole numbers
2​
5:00​
5:00:00​
5​
5​
3​
8:00​
8:00:00​
8​
8​
4​
11:00​
12:00:00​
11​
12​
5​
12:00​
13:30:00​
12​
13.5​
6​
17:00​
21:00:00​
17​
21​
7​
18:00​
22:30:00​
18​
22.5​
8​
23:00​
30:00:00​
23​
30​
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
 
Upvote 0
Hey thanks Jim that might work for what I have, but could get your OT hours in column I and not have column J?
 
Upvote 0
Gentlemen that has been huge thank you so much! It must be Monday that should not have been that difficult haha.
Thank you Both!
 
Upvote 0

Excel 2010
ABCDEF
1Sum736112
2# > 93TotalRegularOT
3Regular6161880
4OT1218990
5880
6990
71293
81293
91596
2a
Cell Formulas
RangeFormula
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top