Calculating Over Time after 40 hours in a week

[Islandlady]

I am looking for assistance on calculating regular hours and overtime hours based on the numbers of hours worked up until that point in the week. Employees may have multiple time entries per day and I need it to slip the hours into regular and overtime once the 40 regular hour requirement has been fulfilled. As you will see with my current formula, when the total hours hits 40 hours all time in that entry goes to overtime, where some should go to regular time and some should go to over time. (See K22 & L22)

I tried to upload a mini sheet but I am having trouble.

Thanks you in advance for your assistance.

 

[Dave Patton]

Consider the following and then adapt to your spreadsheet.

Date and Time 4.xlsm
	G	H	I	J	K
2			Total	Regular	OT
3			34.25	34.25	0.00
4	09:00 AM	11:30 AM	36.75	36.75	0.00
5	11:45 AM	08:15 PM	45.25	40.00	5.25
6
2d

Cell Formulas
Range	Formula
J3:J5	J3	=MIN(I3,40)
K3:K5	K3	=I3-J3
I4:I5	I4	=I3+(H4-G4)*24