Random Times

[Guest]

I need to generate random times of the day between 6am and 6pm. They cannot
be any more than 55 minutes apart. And cannot be between 10:00 am and 10:30
am. I have tried everything I know and cannot come up with anything. Anyone
know of a function I can use to produce this automatically every day?

 

[Bernie Deitrick]

Tiffany,

In Cell A1, enter "Random Times"
In Cell A2, enter

6:00:00 AM

In cell A3, enter the formula:

=A2+RAND()*55/1440

and copy down for 30 or 40 rows.

Then in cell B1, enter the "Use"

In cell B2, enter the formula

=AND(NOT(AND(A2>=TIMEVALUE("10:00:00"),A2<=TIMEVALUE("10:30:00"))),A2<TIMEVA
LUE("6:00:00 PM"))

and copy down to match column A.

Then apply a filter to the table, and select the dropdown for column B, and
select "TRUE"

HTH,
Bernie
MS Excel MVP