Random Times
 

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???

1. post the oringinal solution so I don't have to completely start from
scratch.

2. How many times need to be gererated for each day?

"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???

OK, I got it, interesting little problem

First column header, Check Number, second is Check Time.

Just number the forst column i to say 30.

First cell next the the first check, enter this formula:

=RAND()/24+(6/24)
(to change the start time, change the 6 to the new hour to start)

Cell below that, and copy to every other cell downwards:

=((RAND()*40+15)/24/60)+B2
( to change the minimum time, change the 15. then change the maximum time,
change the 40 in this way: for 45, 45 - 15 is 30, so replace the 40 with 30.)

If you have trouble, post back.


"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???

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???

Hmm... Not sure why you chose RAND()/24 for the first entry. Granted,
Tiffany's problem was ambiguous, but I would have thought that the first
check should happen between 6:00 and 6:55. Yours allows a start as late
as 7:00.

Also, your 2nd formula does not account for there being no checks
allowed between 10:00 and 10:30 am. In order to be "random" and still
meet the no more than 55 minutes, this would require an iterative
process. Of course, I have no idea if that requirement is imposed.



In article ,
Rob wrote:

OK, I got it, interesting little problem

First column header, Check Number, second is Check Time.

Just number the forst column i to say 30.

First cell next the the first check, enter this formula:

=RAND()/24+(6/24)
(to change the start time, change the 6 to the new hour to start)

Cell below that, and copy to every other cell downwards:

=((RAND()*40+15)/24/60)+B2
( to change the minimum time, change the 15. then change the maximum time,
change the 40 in this way: for 45, 45 - 15 is 30, so replace the 40 with 30.)

If you have trouble, post back.

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<TIMEVALUE("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???

OK, you resolved one of the ambiguities (I think - was your A2 example
supposed refer to A1 rather than being a circular reference?), but I'm
not sure about the other.

Perhaps a specific example:

Say a series of random checks is

06:01
06:34
06:57
07:40
08:22
08:37
09:06
09:46

Since there must be 15 minutes between checks, no more than 55, and none
between 10:00 and 10:30, that would seem to indicate that the next check
must happen between 10:31 and 10:41. Is that correct?

If the last check had been 09:40, would that then mean that the next
check would be either 09:55-09:59 or 10:31-10:35?

In either case, that will significantly "unrandomize" the checks -
they'll happen much more frequently in the 10 minutes before or 10
minutes after the 10:00-10:30 window than at any other time of the day.

OTOH, if any randomly generated time between 10:00 and 10:30 could be
skipped, i.e., that the 10:14 check in:

09:06
09:46
10:14
10:59

was skipped, allowing a 1:13 gap between checks, there would be no
compression around 10:00-10:30.



In article ,
"Tiffany" wrote:

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<TIMEVALU
E("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!

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???

Well, setting up the auto filter with true/false seems to prevent that from
happening. I just keep selecting true for the filter and eventually they all
show true. But, as opposed to the system they had set up previously which
took about a half an hour to produce one days checks, I think this works
better.

I used the formula's you gave me and was able to produce times of 9:19 and
then 10:42. I think I've finally figured it out, thanks to you, and can make
the qc dept happy finally!

Thanks so much for your help! I've been working on this for at least a week
or so.

"JE McGimpsey" wrote:

OK, you resolved one of the ambiguities (I think - was your A2 example
supposed refer to A1 rather than being a circular reference?), but I'm
not sure about the other.

Perhaps a specific example:

Say a series of random checks is

06:01
06:34
06:57
07:40
08:22
08:37
09:06
09:46

Since there must be 15 minutes between checks, no more than 55, and none
between 10:00 and 10:30, that would seem to indicate that the next check
must happen between 10:31 and 10:41. Is that correct?

If the last check had been 09:40, would that then mean that the next
check would be either 09:55-09:59 or 10:31-10:35?

In either case, that will significantly "unrandomize" the checks -
they'll happen much more frequently in the 10 minutes before or 10
minutes after the 10:00-10:30 window than at any other time of the day.

OTOH, if any randomly generated time between 10:00 and 10:30 could be
skipped, i.e., that the 10:14 check in:

09:06
09:46
10:14
10:59

was skipped, allowing a 1:13 gap between checks, there would be no
compression around 10:00-10:30.



In article ,
"Tiffany" wrote:

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<TIMEVALU
E("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!