formula to calculate hours minus 1/2 hr lunch if more than 5 hrs of work + overtime

Hi to All,

I am trying to fine tune my time sheet so I don't have to manually subtract 1/2 hr for lunch. I also need it to automatically add overtime hrs if more than 8 hrs are worked a day.I copied a formula from this forum but it still took 1/2 hr off even though they only worked 4 hrs.
Regular hours are from 7am-3:30pm with a 1/2 hr lunch from 12 pm-12:30 pm. On days that employees work from 7 am- 12pm we don't subtract lunch.

I am attaching my timesheet if that will help.

I hope someone can help me with this, I am going crazy.

Thanks
Solray

calc hrs worked.xlsx

Hi ratcat, thank you very much it looks great. didn't think to break up the calcs but it makes a lot of sense. i am going to give it a good look over and hopefully have more feedback.

thanks again
solray

Hi ratcat, I need a little more help on the spreadsheet. If someone works more than 8 hrs a day i need the extra hrs to go to the overtime column and then get added to the final total. As it is now the overtime hrs are going to regular hrs. Is this possible. Thanks for your help again.

Solray

Hi ratcat, I need a little more help on the spreadsheet. If someone works more than 8 hrs a day i need the extra hrs to go to the overtime column and then get added to the final total. As it is now the overtime hrs are going to regular hrs. Is this possible. Thanks for your help again.

Solray

Hi Ratcat, no worries on sounding rude, i know it is possible I don't have a good understanding of the IF formula and don't want to screw it up. What i want is if someone works 9 hrs the 9th hr goes into the overtime column Y. I am going try what you said to do.
Thanks
solray

Hi ratcat , thanks for your help and patients. I figured it out it was super simple, it was there but had just not ben filled all the way down. I really appreciate your help on this my life has gotten a lot easier.