Time duration in block

th081

Board Regular
Joined
Mar 26, 2006
Messages
57
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
ABCDEFGHI
223:00 to 03:00 (4 hours)03:00 to 07:00 (4 Hours)07:00 to 11:0011:00 to 15:0015:00 to 19:0019:00 to 23:0023:00 to 03:00 next day
3Start Date and TimeEnd Date and Time01/11/2020 23:0002/11/2020 03:0002/11/2020 07:0002/11/2020 11:0002/11/2020 15:0002/11/2020 19:0002/11/2020 23:00
401/11/2020 23:3002/11/2020 02:453 hours 15 minzero mins fall in this blockzero mins fall in this blockzero mins fall in this blockzero mins fall in this blockzero mins fall in this blockzero mins fall in this block
501/11/2020 00:2502/11/2020 20:452 hours 35 min4 hours4 hours4 hours4 hours1 hour 45 minzero mins fall in this block
601/11/2020 03:4502/11/2020 04:45zero mins fall in this block1 hourzero mins fall in this blockzero mins fall in this blockzero mins fall in this blockzero mins fall in this blockzero mins fall in this block
702/11/2020 06:5502/11/2020 16:07zero mins fall in this block5 mins4 hours4 hours1 hour 7 minszero mins fall in this blockzero mins fall in this block
Sheet5
Cell Formulas
RangeFormula
D3:I3D3=C3+(4/24)
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,627
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,486
Try:

Book1 (version 1).xlsb
ABCDEFGHI
1
223:00 to 03:00 (4 hours)03:00 to 07:00 (4 Hours)07:00 to 11:0011:00 to 15:0015:00 to 19:0019:00 to 23:0023:00 to 03:00 next day
3Start Date and TimeEnd Date and Time11/1/20 23:0011/2/20 3:0011/2/20 7:0011/2/20 11:0011/2/20 15:0011/2/20 19:0011/2/20 23:00
411/1/20 23:3011/2/20 2:453:150:000:000:000:000:000:00
511/1/20 0:2511/2/20 20:454:004:004:004:004:001:450:00
611/1/20 3:4511/2/20 4:454:001:450:000:000:000:000:00
711/2/20 6:5511/2/20 16:070:000:054:004:001:070:000:00
Sheet7
Cell Formulas
RangeFormula
D3:I3D3=C3+(4/24)
C4:I7C4=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.
 
Solution

th081

Board Regular
Joined
Mar 26, 2006
Messages
57
that partially works for some

Book2.xlsx
ABCDEF
223:00 to 03:00 (4 hours)03:00 to 07:00 (4 Hours)07:00 to 11:0011:00 to 15:00
3Start Date and TimeEnd Date and Time01/11/2020 23:0002/11/2020 03:0002/11/2020 07:0002/11/2020 11:00
401/11/2020 23:3002/11/2020 02:4503:15:0000:00:0000:00:0000:00:00
501/11/2020 00:2502/11/2020 20:4504:00:0004:00:0004:00:0004:00:00
601/11/2020 03:4502/11/2020 04:4504:00:0001:45:0000:00:0000:00:00
702/11/2020 06:5502/11/2020 16:0700:00:0000:05:0004:00:0004:00:00
Sheet5
Cell Formulas
RangeFormula
D3:F3D3=C3+(4/24)
C4:F7C4=MAX(0,MIN(C$3+1/6,$B4)-MAX(C$3,$A4))
 

th081

Board Regular
Joined
Mar 26, 2006
Messages
57

ADVERTISEMENT

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,486
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,627
Not sure in which way Eric's solution from Post #3 is different from mine from Post #2 :unsure:
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,486
Not sure in which way Eric's solution from Post #3 is different from mine from Post #2 :unsure:
Exactly the same! You posted first, but since I had already dummied up the whole sample, I went ahead and posted it.
 

th081

Board Regular
Joined
Mar 26, 2006
Messages
57
thank you both, that works, i was even getting confused with the example i posted.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,155
Members
412,574
Latest member
shadowfighter666
Top