Random select time from game clock

[catiexcel]

For quality analysis I want to select a random 20 minutes from a 80-minute rugby game. How do I customise the '=TEXT(RAND(), "MM:SS")' so that its giving me a starting number between 0.00 and 80.00?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=RANDARRAY(1,1,TIME(0,0,0),TIME(1,20,0))

and format the cell as [mm]:ss

 

[JamesCanale]

One of these gives the random second, the other gives the random whole minute:

MrExcelPlayground16.xlsx
	A
9	69:43
10	27:00
Sheet14

Cell Formulas
Range	Formula
A9	A9	=TEXT(RAND()*80/1440,"[mm]:ss")
A10	A10	=TEXT(INT(RAND()*80)/1440,"[mm]:ss")

 

[Joe4]

James,

If they want to select a complete random 20 minute block from an 80 minute game, wouldn't you want the start time to be capped at 60 minutes?
If you start the random block at any point after 60 minutes, you cannot get a full, complete 20 minute block.

 

[JamesCanale]

I had the idea that they wanted 20 random one minute blocks. That seems silly now.

 

[catiexcel]

James,

If they want to select a complete random 20 minute block from an 80 minute game, wouldn't you want the start time to be capped at 60 minutes?
If you start the random block at any point after 60 minutes, you cannot get a full, complete 20 minute block.

I actually realised this when I tried a few solutions, it does make sense to cap it to 60 so I've adapted that.

 

[catiexcel]

Hi & welcome to MrExcel.
How about

=RANDARRAY(1,1,TIME(0,0,0),TIME(1,20,0))

and format the cell as [mm]:ss

Thank you! This works great for what I need. I'm wondering now is there possibly a way to generate two 10-minute blocks in the same 80-mins but don't overlap?

 

[Fluff]

How about

=RANDARRAY(1,1,TIME(0,0,0),TIME(0,30,0))

and

=RANDARRAY(1,1,TIME(0,40,0),TIME(1,10,0))

 

[JamesCanale]

This is ridiculous, but there is not constraint on when the two 10-minute periods start happen, except not to overlap. They could be the first two ten minutes.

=LET(a,RANDARRAY(1,1,TIME(0,0,0),TIME(1,10,0)),b,RANDARRAY(10,1,TIME(0,0,0),TIME(1,10,0)),c,ABS(b-a),d,FILTER(b,c>10/1440),e,INDEX(d,1),f,IF(SEQUENCE(2)=1,a,e),f)

 

[catiexcel]

This is ridiculous, but there is not constraint on when the two 10-minute periods start happen, except not to overlap. They could be the first two ten minutes.

=LET(a,RANDARRAY(1,1,TIME(0,0,0),TIME(1,10,0)),b,RANDARRAY(10,1,TIME(0,0,0),TIME(1,10,0)),c,ABS(b-a),d,FILTER(b,c>10/1440),e,INDEX(d,1),f,IF(SEQUENCE(2)=1,a,e),f)

That'll do it!