Splitting hours worked into multiple shift patterns

[esset]

I am trying to allocate hours worked to multiple shift patterns. I thought I found a solution in a different thread however it is not 100% right.
In some cases the formulas don't work.
Any help would be appreciated.

shifts-04c.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	time 1	00:00
2	time 2	06:00	night
3	time 3	07:00	day
4	time 4	08:00	day
5	time 5	18:00	day
6	time 6	20:00	evening
7	time 7	24:00	evening
8
9				00:00-06:00	06:00-07:00	07:00-08:00	08:00-18:00	18:00-20:00	20:00-00:00
10				night	day	day	day	evening	evening
11
12		start	end	shift1	shift2	shift3	shift4	shift5	shift6	total formula	total sum	verschil
13		06:30	05:30	05:30	00:30	01:00	10:00	02:00	04:00	23:00	23:00	00:00
14		11:00	22:00	00:00	00:00	00:00	07:00	02:00	02:00	11:00	11:00	00:00
15		09:00	02:15	02:15	00:00	00:00	09:00	02:00	04:00	17:15	17:15	00:00
16		02:00	19:30	04:00	01:00	01:00	10:00	01:30	00:00	17:30	17:30	00:00
17		20:00	07:00	06:00	00:00	00:00	00:00	00:00	04:00	11:00	10:00	01:00
18		09:00	18:00	00:00	00:00	00:00	09:00	00:00	00:00	09:00	09:00	00:00
19		08:00	12:15	00:00	00:00	00:00	04:15	00:00	00:00	04:15	04:15	00:00
20		12:45	17:30	00:00	00:00	00:00	04:45	00:00	00:00	04:45	04:45	00:00
21		18:30	06:45	06:00	00:00	00:00	00:00	01:30	04:00	12:15	11:30	00:45
22		08:00	07:30	06:00	00:00	00:00	10:00	02:00	04:00	23:30	22:00	01:30
23		22:00	20:45	06:00	00:00	00:00	00:00	00:00	02:00	22:45	08:00	14:45
24		06:30	05:30	05:30	00:30	01:00	10:00	02:00	04:00	23:00	23:00	00:00
25		06:30	05:30	05:30	00:30	01:00	10:00	02:00	04:00	23:00	23:00	00:00
Blad1

Cell Formulas
Range	Formula
D9	D9	=IF($B$2="","",TEXT(B1,"uu:mm")&"-"&TEXT(B2,"uu:mm"))
E9	E9	=IF($B$3="","",TEXT(B2,"uu:mm")&"-"&TEXT(B3,"uu:mm"))
F9	F9	=IF($B$4="","",TEXT(B3,"uu:mm")&"-"&TEXT(B4,"uu:mm"))
G9	G9	=IF($B$5="","",TEXT(B4,"uu:mm")&"-"&TEXT(B5,"uu:mm"))
H9	H9	=IF($B$6="","",TEXT(B5,"uu:mm")&"-"&TEXT(B6,"uu:mm"))
I9	I9	=IF($B$7="","",TEXT(B6,"uu:mm")&"-"&TEXT(B7,"uu:mm"))
D10	D10	=C2
E10	E10	=C3
F10	F10	=C4
G10	G10	=C5
H10	H10	=C6
I10	I10	=C7
D13:D25	D13	=IF($B$2="","",IF(COUNT($B13:$C13)=2,MAX(0,MIN($B$2,$C13+($C13<$B13))-MAX($B13,$B$1))+MAX(0,MIN($B$2+1,$C13+($C13<$B13))-1),""))
E13:E25	E13	=IF($B$3="","",IF(COUNT($B13:$C13)=2,MAX(0,MIN($B$3,$C13+($C13<$B13))-MAX($B13,$B$2)),""))
F13:F25	F13	=IF($B$4="","",IF(COUNT($B13:$C13)=2,MAX(0,MIN($B$4,$C13+($C13<$B13))-MAX($B13,$B$3)),""))
G13:G25	G13	=IF($B$5="","",IF(COUNT($B13:$C13)=2,MAX(0,MIN($B$5,$C13+($C13<$B13))-MAX($B13,$B$4)),""))
H13:H25	H13	=IF($B$6="","",IF(COUNT($B13:$C13)=2,MAX(0,MIN($B$6,$C13+($C13<$B13))-MAX($B13,$B$5)),""))
I13:I25	I13	=IF($B$7="","",IF(COUNT($B13:$C13)=2,MAX(0,MIN($B$7,$C13+($C13<$B13))-MAX($B13,$B$6)),""))
J13:J25	J13	=IF(COUNT($B13:$C13)=2,$C13-$B13+($C13<$B13),"")
K13:K25	K13	=SUM(D13:I13)
L13:L25	L13	=J13-K13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L13:L25	Cell Value	>0	text	NO

 

[esset]

I found my answer here.
Total hours that fall between two times

 

[esset]

Apparently that is not correct all the time either.

 

[esset]

I finally figured it out. Here is the formula that works in all situations:

Map2
	A	B	C
1	08:00
2	17:00
3
4	start	end	shift
5	19:00	09:30	01:30
6	11:30	22:00	05:30
7	06:00	21:00	09:00
Blad1

Cell Formulas
Range	Formula
C5:C7	C5	=IF($A5<$B5,MAX(0,MIN($B5,$A$2)-MAX($A5,$A$1)),MAX(0,$A$2-MAX($A5,$A$1))+MAX(0,MIN($B5,$A$2)-$A$1))