Work out hours in different time slots

[Lmaonade]

Hi,

I want to create a tool in excel that will split a working shift depending on start and end time.

Book4
	B	C	D	E		G	H	I	J	K
3		From	To
4	Night Hours:	20:00	06:00					Week day	Week Night / Saturday	Sunday / Bank Holiday
5
6		Start	End	Break		Total
7	Monday:	13:45	21:30	0.00		7.75
8	Tuesday:	00:00	00:00	0.00		0.00
9	Wednesday:	07:45	20:00	0.50		11.75
10	Thursday:	19:00	07:00	0.50		11.50
11	Friday:	19:45	07:45	1.00		11.00
12	Saturday:	18:00	04:00	0.75		9.25
13	Sunday:	19:45	08:00	1.50		10.75
14	Bank Holiday:	00:00	00:00	0.00		0.00
Sheet1

Cell Formulas
Range	Formula
G7:G14	G7	=(F7*24)-E7

So you can see the night shift hours are 20:00 to 06:00. How would I create a rule in columns I, J and K to split each shift into how many hours should be in each section? Any breaks would be taken at 6 hours into the shift so that's where that would come off.

So row 7 (Monday) should split it as: 6.25 hours in Week day, 1.5 hours in Week Night / Saturday
row 13 (Sunday) should split it as 2 hours in week day (The 2 hours after 6am) 4.25hrs Sunday / Bank Holiday (The 19:45 to 00:00) and then 4.5 hours at Week Night Saturday (00:00 to 06:00 minus the break).

Saturday and Sunday rates are 00:00 to 00:00 (24 hours), Bank Holiday should be set the same way as Sunday, for example if someone works a Bank Holiday night - Anything after Midnight moves on to a normal week night rate until 06:00.

Hope this makes sense. If you need any more info please let me know and I really appreciate any help.

Thanks,
Andy