How can I get my formula to deduct lunch breaks based on hours worked?

Hi there,

I have created a spread sheet to record staff hours worked. The shifts worked are recorded into the cell as 09:00-18:00 and I have then created the following formula to total the hours worked each week;

=SUM(IF(ISNUMBER(LEFT(C4:I4,5)+0),RIGHT(C4:I4,5)-LEFT(C4:I4,5)-(RIGHT(C4:I4,5)-LEFT(C4:I4,5)>=1/3)*1.5/24))*24

This currently says that if they work more than 8 hours they should be deducted 1.5hrs for a break, however, I also want it to say if you work less than 8 hours you only get 0.5 hours break.

I have tried every which way to add this in but it wont work...Please help?

Thanks

Sorry, it wont upload my file.

C5 contains the hours worked on Monday in the format 10:00-19:00
D5 contains Tuesdays hours 10:00-19:00
E5 contains Wednesdays hours, which in this case is day off, so contains D/O.
F5 contains Thursdays hours, and so on...

so in J5 I have my formula to calculate how many hours have been worked, minus the 1.5 hrs break, but in some cases they will work 6 hours and will only have a half hour break.

Hi,

Thanks for coming back to me.

This hasn't worked though

It now gives me the result of 1.52 when it should be 36.5

Any ideas?