Tiffany,

Simply change:

=A2+RAND()*55/1440

to

=A2+(15 + RAND()*40)/1440

HTH,
Bernie
MS Excel MVP


"Tiffany" wrote in message
...
Ok. This is what I started with last week:

A1 is 6:00, B1 is

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

A2 is =A2+RAND()*55/1440

This provides random times between 6 am and 6 pm that are at least 55
minutes apart and not between 10 and 10:30 am. The problem is, I cannot
get
them to be at least 15 minutes apart. Somtimes the random times produced
are
like 11:00, then 11:02 then 11:05. That's not enough time for the QC
techs
to do their actual checks.

I do not believe the first check is at 6:00 am, as this is not a random
time
every day. The first check has to be between 6 am and 6:55. The reason I'm
doing this is to make it almost automated. I have not tried the formula's
you
gave me, but I will. I wanted to explain the situation to you first.

Thanks for your help!

"JE McGimpsey" wrote:

Your problem statement is somewhat ambiguous.

Do your *checks* start at 6:00 am (i.e, 6:00 am every day), or should
the first check start between 6:00 am and, say, 6:55 am?

If no checks can happen between 10:00 am and 10:30 am, are you still
required to meet the 55 minute maximum? If so, you'll probably need an
iterative process and checks will be clustered just before 10:00 and
just after 10:30.


If you have to have one check between 6:00 and 6:55, and you can just
skip any checks falling between 10:00 and 10:30, you can use something
like this:

A1: =TIME(6, RAND()*55, 0)
A2: =A1 + TIME(0, 15+RAND()*40, 0)

and copy down through A49, discarding anything after 6:00 pm or between
10:00 and 10:30.

In article ,
"Tiffany" wrote:

Ok. I guess the government had to go and make this even more difficult
for
me.

I need to figure out how to get random times for check during one day
using
an Excel spreadsheet.

They start at 6:00 a.m. up until about 6:00 p.m. They have to be no
more
than 55 minutes apart, but less than 15 min apart. They cannot be
between
10:00 am and 10:30 am.

I received a response to my last question which was between 6 and 6pm,
no
more than 55 min apart and not between 10 and 10:30 am. I set it up.
It works
wonders. But, now I'm told they have to be at least 15 min apart. I
tried to
adjust the formula accordingly, but I just can't figure it out.

Can anyone help me please???