Time duration in block

[th081]

Can someone please help me with a time formula. I have a start date and time in columns A and B and then blocks of 4 hour chunks in column C onwards. I would a formula that will tell me how many hours (up to a max of 4) goes in each column chunk. I am just getting myself tied up in knots so help would be much appreciated.

Book2.xlsx
	A	B	C	D	E	F	G	H	I
2			23:00 to 03:00 (4 hours)	03:00 to 07:00 (4 Hours)	07:00 to 11:00	11:00 to 15:00	15:00 to 19:00	19:00 to 23:00	23:00 to 03:00 next day
3	Start Date and Time	End Date and Time	01/11/2020 23:00	02/11/2020 03:00	02/11/2020 07:00	02/11/2020 11:00	02/11/2020 15:00	02/11/2020 19:00	02/11/2020 23:00
4	01/11/2020 23:30	02/11/2020 02:45	3 hours 15 min	zero mins fall in this block	zero mins fall in this block	zero mins fall in this block	zero mins fall in this block	zero mins fall in this block	zero mins fall in this block
5	01/11/2020 00:25	02/11/2020 20:45	2 hours 35 min	4 hours	4 hours	4 hours	4 hours	1 hour 45 min	zero mins fall in this block
6	01/11/2020 03:45	02/11/2020 04:45	zero mins fall in this block	1 hour	zero mins fall in this block	zero mins fall in this block	zero mins fall in this block	zero mins fall in this block	zero mins fall in this block
7	02/11/2020 06:55	02/11/2020 16:07	zero mins fall in this block	5 mins	4 hours	4 hours	1 hour 7 mins	zero mins fall in this block	zero mins fall in this block
Sheet5

Cell Formulas
Range	Formula
D3:I3	D3	=C3+(4/24)

 

[Tetra201]

Try placing the following formula in cell C4, custom-formatting it as "h:mm", and drag-copying it down and to the right as needed:

=MAX(0,MIN(C$3+1/6,$B4)-MAX(C$3,$A4))

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1
2			23:00 to 03:00 (4 hours)	03:00 to 07:00 (4 Hours)	07:00 to 11:00	11:00 to 15:00	15:00 to 19:00	19:00 to 23:00	23:00 to 03:00 next day
3	Start Date and Time	End Date and Time	11/1/20 23:00	11/2/20 3:00	11/2/20 7:00	11/2/20 11:00	11/2/20 15:00	11/2/20 19:00	11/2/20 23:00
4	11/1/20 23:30	11/2/20 2:45	3:15	0:00	0:00	0:00	0:00	0:00	0:00
5	11/1/20 0:25	11/2/20 20:45	4:00	4:00	4:00	4:00	4:00	1:45	0:00
6	11/1/20 3:45	11/2/20 4:45	4:00	1:45	0:00	0:00	0:00	0:00	0:00
7	11/2/20 6:55	11/2/20 16:07	0:00	0:05	4:00	4:00	1:07	0:00	0:00
Sheet7

Cell Formulas
Range	Formula
D3:I3	D3	=C3+(4/24)
C4:I7	C4	=MAX(0,MIN($B4,C$3+4/24)-MAX($A4,C$3))

Although not all the results match your example. Check to see if this works for you.

 

[th081]

that partially works for some

Book2.xlsx
	A	B	C	D	E	F
2			23:00 to 03:00 (4 hours)	03:00 to 07:00 (4 Hours)	07:00 to 11:00	11:00 to 15:00
3	Start Date and Time	End Date and Time	01/11/2020 23:00	02/11/2020 03:00	02/11/2020 07:00	02/11/2020 11:00
4	01/11/2020 23:30	02/11/2020 02:45	03:15:00	00:00:00	00:00:00	00:00:00
5	01/11/2020 00:25	02/11/2020 20:45	04:00:00	04:00:00	04:00:00	04:00:00
6	01/11/2020 03:45	02/11/2020 04:45	04:00:00	01:45:00	00:00:00	00:00:00
7	02/11/2020 06:55	02/11/2020 16:07	00:00:00	00:05:00	04:00:00	04:00:00
Sheet5

Cell Formulas
Range	Formula
D3:F3	D3	=C3+(4/24)
C4:F7	C4	=MAX(0,MIN(C$3+1/6,$B4)-MAX(C$3,$A4))

 

[th081]

but C5 should be 03:00 - 00:25 etc

 

[Eric W]

Assuming we have different date formats, and A5 is November 1st, 00:25 am, and B5 is November 2nd, 20:45 pm, that's a 44 hour, 20 minute range. Then from C3 we have November 1st 23:00 pm going to November 2nd, 3:00 am. This time frame is completely within the first range, so it comes out to 4 hours overlap, which is what C5 says.

 

[Tetra201]

but C5 should be 03:00 - 00:25 etc

... for which A5 should be 2-Nov-2020 0:25

 

[Tetra201]

Not sure in which way Eric's solution from Post #3 is different from mine from Post #2

 

[Eric W]

Not sure in which way Eric's solution from Post #3 is different from mine from Post #2

Exactly the same! You posted first, but since I had already dummied up the whole sample, I went ahead and posted it.

 

[th081]

thank you both, that works, i was even getting confused with the example i posted.