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

		Standard Shift Hrs	Start Time	Normal Shift End	Finish Time	Total Hours	Decimal Hours	Total Extra Hours Worked	Additional Hours (Normal Shift to 10 hours	1.5 time (10 hrs to 12 hrs)	2 x time (12 hrs onwards)	Shift Rate	Hourly Rate	1.5 x Rate	2 x Rate	Additional Hours $$	1.5 x $$	2 x $$	Total Overtime
Jane Bloggs		8	16:30	0:30	4:00	11:30	11.50	3.50	3.50	1.50	-0.50	$200.00	$25.00	$37.50	$50.00	$87.50	$56.25	-$25.00	$118.75
Joe Bloggs		6	16:30	22:30	1:15	8:45	8.75	2.75	2.75	-1.25	-3.25	$75.00	$12.50	$18.75	$25.00	$34.38	-$23.44	-$81.25	-$70.31

<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>
</tbody>

 

[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,"")