trying to separate overtime and regular time into 3 cells

[asawanilea]

trying to separate overtime and regular time into 3 cells. morning overtime, regular time, and after hours overtime in Excel. I go from site to site, and each work order gets its own row. I am subtracting end time from start time on each row. If a time goes through 08:00am, i.e. 06:00 - 10:00. then I need one cell calculation to say 2 hours overtime and the next cell calculation to say 2 hours of regular pay. same thing for the third cell except regular pay end at 5:00pm, i.e. 4:00pm to 8:00pm, 1 hours of regular pay and 3 hours of overtime. I thought that I had it with =SUMPRODUCT, but computer crashed. been three weeks and I cannot get near to a solution. Also I need the the regular pay calculation to calculate only between 8:00am and 5:00pm no matter what is in the time calculation, sometimes I work 16 hours days. i am trying to subtract end time from the start time

 

[BSALV]

myHours.xlsx
	A	B	C	D	E	F	G
1				00:00:00	08:00:00	16:00:00	24:00:00
2	start	end	hours	morning overtime	Regular time	Evening overtime
3	04:30:00	23:22:11	18:52:11	03:30:00	08:00:00	07:22:11
4	08:31:00	16:12:00	07:41:00	00:00:00	07:29:00	00:12:00
5			-	-	-	-
6			-	-	-	-
7			-	-	-	-
8			-	-	-	-
9			-	-	-	-
10			-	-	-	-
Blad1

Cell Formulas
Range	Formula
C3:C10	C3	=IF(COUNT(A3:B3)=2,B3-A3,"-")
D3:F10	D3	=IF(ISNUMBER(C3),MAX(0,MIN(E$1,$B3)-MAX(D$1,$A3)),"-")