time durations within time periods (greater than/less than?)

Help if you can, it's beaten me!

Table a
game 1 01:23 01:57
game 2 02:34 02:56
game 3 03:04 03:34

Table b
block 1 01:23 01:34
block 2 01:44 01:52
block 3 01:56 02:45
block 4 02:50 03:34

I want a formula for many minutes a game has been played within a block. Eg. game 1 is
block 1 - 11mins
block 2 - 8 mins
block 3 - 1 min
=20 mins in total.

I have been trying all kinds of formulas and can't figure it out. I want to be able to calculate total minutes for game 1, game 2, & game 3 from the times within the blocks.

Help appreciated

Thanks..

Originally Posted by rosieb13

Help if you can, it's beaten me!

Table a
game 1 01:23 01:57
game 2 02:34 02:56
game 3 03:04 03:34

Table b
block 1 01:23 01:34
block 2 01:44 01:52
block 3 01:56 02:45
block 4 02:50 03:34

I want a formula for many minutes a game has been played within a block. Eg. game 1 is
block 1 - 11mins
block 2 - 8 mins
block 3 - 1 min
=20 mins in total.

I have been trying all kinds of formulas and can't figure it out. I want to be able to calculate total minutes for game 1, game 2, & game 3 from the times within the blocks.

Help appreciated

Thanks..

Assume game 1 start time in B2 and end time in C2, and game play within a block start time in B8 and end time in C8

=MIN($C$2,C8)-MAX($B$2,B8)
copy down

Thanks but this didn't work,it only returned the value of the 1st block.

TIME IN TIME OUT DURATION
game 1 0:01 0:58 0:06 answer should be 0:11
game 2 0:56 1:50 answer should be 0:21
game 3 1:51 1:59 answer should be 0:08



Block 1 0:01 0:07 0:06
Block 2 0:53 1:07 0:14
Block 3 1:40 1:59 0:19
Block 4
Block 5
Block 6


I have tried this formula
=IF(E1469>E1477,SUM(E1477-D1477)+(E1469-D1478),IF(E1469>D1479,SUM(E1477-D1477)+(E1478-D1478)+(E1469-D1479),IF(IF(E1469>D1473,E1469+1-D1469,E1469-D1469)=0,"",IF(E1469>D11473,E1469+1-D1469,E1469-D1469))))
but this only works across 2 blocks.

i need a formula that calcultes possiblity of game being played in 1 block or 6...
Further suggestions welcome

Thank you!

I think that teethless mama’s formula is along the right lines. You also need to have block 2 start and end times in B9 and C9, block 3 in B10 and C10 and so on down, then the formula goes in D8 copied down. I’ve added an IF function to give a blank when there are no hours in that block

=IF(OR(B$2>=C8,C$2<=B8),"",MIN(C8,C$2)-MAX(B8,B$2))