# Time duration in block

#### th081

##### Board Regular
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)

### 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
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
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.

#### th081

##### Board Regular
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

ADVERTISEMENT

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

#### Eric W

##### MrExcel MVP
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

ADVERTISEMENT

but C5 should be 03:00 - 00:25 etc
... for which A5 should be 2-Nov-2020 0:25

#### Tetra201

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

#### Eric W

##### MrExcel MVP
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

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

Replies
3
Views
37
Replies
1
Views
70
Replies
2
Views
42
Replies
8
Views
258
Replies
3
Views
130

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