Calculating overtime hours on a daily and weekly basis

Hi, I'm very new to excel and don't understand big words so please be gentle with me.

I need a timesheet that calculates overtime based on a 38 Monday to Friday week.
We work two 12 hour shifts starting at 7am and 7pm seven days a week.
Saturdays are 1.5 for the first 2 hours, double time for the next 10 hours. Sundays are double time all day. I have them covered.
Monday to Friday is 8 hours normal time, 2 hours (max) at 1.5 and double for any hours therafter.
Pretty simple so far, however on Friday it is 6 hours normal time (to reach the 38hour target) , 1.5 for the next 2 hours and double time thereafter.
The tricky bit is, we don't always work 8 hours each day so the normal hours worked on a Friday may be up to 8 hours to meet the 38 hour week requirement before overtime begins.

Basically what I need is a formula that will give me up to a maximum of 6 hours on a friday if 32 normal hours have already been worked, or a maximum of 8 hours if the weekly normal time is less than 32 hours.
Thanks for any help you can give me
kels timesheet2.xls

I think the formula you want in G6 is =MAX(6,MIN(8,38-SUM(G2:G5)))

Hi,

I have replaced MAX and MIN with IF.

See enclosed.

Does it do the job???

Reece

ahhh, I can get back in. Sorry for the slow response, I was experiencing a Database Error.
Reece, thanks but no it doesn't work.

Andrew, thankyou, it's almost perfect except if the employee doesn't work the Friday or perhaps only does 3 hours, I still get a total of 6 hours worked.
How can I get it to except F6 as the total if it's value is less than 6?
Many thanks
Fuzz