Formula to calculate/split out hours in range

[MrMaker]

Morning,

I need a little bit of help please.

Simply, I have a start date/time and an end time/date, and I need to split out the total number of hours (or decimal days) in the range so that they are apportioned to each day that they fall in:

Data:

A2) arrived date/time 01/02/2018 09:00

B2) end date/time 03/02/2018 15:30

Output Requirements:

C2 ) how many hours in the above were on 01/02/18 (i.e. between 01/02/2018 09:00 and 01/02/2018 23:59)

D2) how many hours in the above were on 02/02/18 (i.e. between 02/02/2018 00:00 and 02/02/2018 23:59)

E2) how many hours in the above were on 03/02/18 (i.e. between 03/02/2018 00:00 and 03/02/2018 15:30)

Hopefully this makes sense and thank you in advance

 

[VBA Geek]

Try:

Book1
	A	B	C	D	E	F	G
1
2		2/1/2018 9:00
3		2/3/2018 18:00
4
5			start	2/1/2018 9:00	2/2/2018 0:00	2/3/2018 0:00	2/3/2018 15:30
6			end	2/2/2018 0:00	2/3/2018 0:00	2/3/2018 15:30	2/3/2018 23:00
7			# of hours	15.0	24.0	15.5	2.5
Sheet4

Cell Formulas
Range	Formula
D7	=MAX(0,MIN(D6,$B$3)-MAX(D5,$B$2))*24
E7	=MAX(0,MIN(E6,$B$3)-MAX(E5,$B$2))*24
F7	=MAX(0,MIN(F6,$B$3)-MAX(F5,$B$2))*24
G7	=MAX(0,MIN(G6,$B$3)-MAX(G5,$B$2))*24

 

[MrMaker]

Lovely stuff, thanks very much!