Using RANDBETWEEN

[webwyze]

Hi,
I'm trying unsuccessfully, using various connotations of RANDBETWEEN, to enter a random time between say 20:18 and 20:30 (hh:mm) or 00:05 and 00:15 (If I use 20:18 I get a circular reference error.)
I can insert a random numbers, =RANDBETWEEN(1,5) without a problem but if I try =RANDBETWEEN(20.18,20.30) I get 00.00.
I've converted the times to numbers =RANDBETWEEN(0.8458333333,0.8541666667) and still get 00.00

Any help would be greatly appreciated.

Thanks,
Brian

[Norie]

Brian

RANDBETWEEN is for integers but you could still use it, along with the TIME function, to do what you want.

For between 20:18 and 20:30,

=TIME(20, RANDBETWEEN(18,30),0)

and between 00:05 and 00:15

=TIME(00, RANDBETWEEN(5, 15),0)

[shg]

RANBETWEEN accepts and returns integer values.

	A	B
1	20:22	A1: =RANDBETWEEN("20:18"*1440, "20:30"*1440)/1440
2	20:25
3	20:25
4	20:22
5	20:23
6	20:21
7	20:29
8	20:18
9	20:22
10	20:18

[webwyze]

Norie,
Thank you, thank you, thank you.
I've been struggling with this, on and off for the last 3 days and couldn't even get close to it, you're a life saver.

[webwyze]

Thanks very much shg,
works perfectly, will you kindly explain to me the relevance of the 1440 for me amending the formulae to suit various times.

[shg]

1440 is the number of minutes in a day -- it converts time to minutes.

[webwyze]

So it's static, thank you shg, you're very helpful and I appreciate your help immeasurably.

Kind regards,

Brian.