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.
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.