Need to calculate hours worked from normal shift until 10 hour for overtime calculation

[tanyaslater]

Hi there

I'm struggling to find the right formula for the Additional Hours Worked column J in this spreadsheet. I need the formula to calculate overtime adn the different number of hours between the end of a standard shift Column C and 10 hours which will then allow columns K & L to calculate the 1.5 x time and double time rates accordingly.

I urgently need to get this resolved so ANY help whatsoever would be much appreciated.

Many thanks

[TABLE="width: 1242"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Standard Shift Hrs[/TD]
[TD]Start Time[/TD]
[TD]Normal Shift End[/TD]
[TD]Finish Time[/TD]
[TD]Total Hours[/TD]
[TD]Decimal Hours[/TD]
[TD]Total Extra Hours Worked[/TD]
[TD] Additional Hours (Normal Shift to 10 hours [/TD]
[TD]1.5 time (10 hrs to 12 hrs)[/TD]
[TD]2 x time (12 hrs onwards)[/TD]
[TD] Shift Rate [/TD]
[TD] Hourly Rate [/TD]
[TD] 1.5 x Rate [/TD]
[TD] 2 x Rate [/TD]
[TD] Additional Hours $$ [/TD]
[TD] 1.5 x $$ [/TD]
[TD] 2 x $$ [/TD]
[TD]Total Overtime[/TD]
[/TR]
[TR]
[TD]Jane Bloggs[/TD]
[TD][/TD]
[TD]8[/TD]
[TD="align: right"]16:30[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]4:00[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]11.50[/TD]
[TD="align: right"]3.50[/TD]
[TD="align: right"]3.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]-0.50[/TD]
[TD="align: right"] $200.00[/TD]
[TD="align: right"] $25.00[/TD]
[TD="align: right"] $37.50[/TD]
[TD="align: right"] $50.00[/TD]
[TD="align: right"] $87.50[/TD]
[TD="align: right"] $56.25[/TD]
[TD="align: right"]-$25.00[/TD]
[TD="align: right"] $118.75[/TD]
[/TR]
[TR]
[TD]Joe Bloggs[/TD]
[TD][/TD]
[TD]6[/TD]
[TD="align: right"]16:30[/TD]
[TD="align: right"]22:30[/TD]
[TD="align: right"]1:15[/TD]
[TD="align: right"]8:45[/TD]
[TD="align: right"]8.75[/TD]
[TD="align: right"]2.75[/TD]
[TD="align: right"]2.75[/TD]
[TD="align: right"]-1.25[/TD]
[TD="align: right"]-3.25[/TD]
[TD="align: right"] $75.00[/TD]
[TD="align: right"] $12.50[/TD]
[TD="align: right"] $18.75[/TD]
[TD="align: right"] $25.00[/TD]
[TD="align: right"] $34.38[/TD]
[TD="align: right"]-$23.44[/TD]
[TD="align: right"]-$81.25[/TD]
[TD="align: right"]-$70.31[/TD]
[/TR]
</tbody>[/TABLE]

 

[Beyond_avarice]

So it appears that hours between 8-10 are at the Hourly rate. Thus your additional hours at standard rate should be 2.
Enter this in Column "J"

=IF(H2>10,2,"")

But your example shows "Additional Hours" of 3.5. For Joe; should it be .75?

=IF(H2>8,H2-8,"")