subtract time from rand()

[lezawang]

Hi
I want to generate random time. So what I tried is that

A1 = 24:00:00-rand()

But I got error message

but when entered the 24:00:00 in A1 and then in B1 I did this

B1 = A1 - rand()

Excel accept that! Why is that? Thank you.

 

[Phuoc]

Should be:

"24:00:00"-rand()

 

[joeu2004]

You don't say what time resolution that you want. I presume seconds, since you write 24:00:00.

Ostensibly, random time can derived by simply:

=RAND()

The reason is: Excel time is stored as a fraction of a day (1). So, that formula is equivalent to =1*RAND(), which is equivalent to ="24:00"*RAND().

But those formulas have a resolution that is an infinitesimal fraction of a second.

In order to limit the resolution, write:

=--TEXT(RAND(), "[h]:m:s")
or
=--TEXT(ROUND(RAND()*1440,0)/1440, "[h]:m")

formatted as Custom [h]:mm:ss or [h]:mm .

The use of --TEXT(...) ensures that the result has the same binary representation as the apparent time constant.

The first formula works because Excel rounds to the second. The "[h]" format is needed instead of "h" in case time rounds to 24:00:00.

In the second formula, we cannot use simply =--TEXT(RAND(), "[h]:m") because Excel does not round to the minute.

PS.... Another method:

=--(RANDBETWEEN(0,23) & ":" & RANDBETWEEN(0,59) & ":" & RANDBETWEEN(0,59))

But that will generate only up to 23:59:59, not 24:00:00.