Timesheet Self Calculating

[Gwenalemap]

In our State hours need to tally up to 40 hours before an employee gets paid overtime. My Excel spreadsheet currently adds up overtime daily (I have an OT column). Here is a sample of my columns:

<TABLE style="WIDTH: 833pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1108><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=6 width=61><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=2 width=61><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 32.25pt; mso-height-source: userset" height=43><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 78pt; HEIGHT: 32.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_235526 class=xl64 height=43 width=104>Date/Notes</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 93pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=124></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=61>Start</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=61>Lunch Out</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=61>Lunch In</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=61>End</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=61>Total Hours</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=61>Worked Hours</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=61>Regular Hours</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=61>OT Hours</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=64>$/Hr</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 51pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=68>Reg $</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=64>OT $</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 51pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=68>Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e5e0ec; WIDTH: 96pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=128 colSpan=2>Pay Period Total</TD></TR></TBODY></TABLE>
I need to break this sheet into weeks. But we are looking for a formula that will compute OT after 40 hrs that will work in one week.
Here are the formulas I am using; Total Hours =(F4-C4+(F4<C4) ) *24
Worked Hours =((F4-C4+(F4<C4)) - (E4-D4+(E4<D4)))*24
Regular Hours =MIN($H$2,H4)
OT Hours =MAX(0,H4-$H$2)
In the cell above worked hours I have the number 8
In the cell above $/Hr I have 1.5
As it is now, OT will compute if the employee works more than 8 hours for the day.
The rest of the week, the employee may only work 8 hours or less so, no overtime should be paid.
Thank you!