PDA

View Full Version : formula help



austenr
02-28-2018, 12:56 PM
I need to change this to generate random times from 0:00 to 5:00 in minutes.



=TEXT(RAND()*(9-6)/24+6/24,"MM:SS")


Can you use the BETWEEN in this?

p45cal
02-28-2018, 04:45 PM
=RAND()/288
formatted as time or
=TEXT(RAND()/288,"MM:SS")

If that 0:00 to 5:00 is midnight to 5am then
=MROUND(RAND()*5/24,1/1440)
formatted as you will.

austenr
02-28-2018, 04:52 PM
Thanks. what does the /288 do?

p45cal
02-28-2018, 04:59 PM
288 is 24*12
24 hours in the day, 12 is the number of 5-minutes in an hour.
So 288 5-minute periods in a day.
Because RAND() returns a value from 0 to 1, divide that max (1) by 288 returns 5 minutes (the max). Any lower value returned by RAND() returns any time from 0 to 5 minutes.

austenr
03-03-2018, 02:28 PM
thanks