I don't see how you're going to do it with simple Excel functionality.There's a
couple of techniques you could use in the randomization.
For instance:You're using time rounded to the minute.There are 1440 minutes in
a day (60 min/hr * 24 hrs/day)7:00 am is minute 42021:00 is minute 1260.
So, you could use RandBetween(420,1260) to get a random minute between 7:00 and
21:00.
Then, /1440 gives you the fractional part of a day (which is how Excel stores
time).then, display it as hh:mm
=RANDBETWEEN(420,1260)/1440
The problem is with the restriction of maintaining 30 minutes of separation.
Since the data isn't sorted, you cannot use vlookup to locate the nearest
values.
Now, you COULD "seed" the minutes of the first data record in the set of
16.(making the starting minute "random")Then always offset it by 30 regardless
of the hour.but then you run the risk of duplicating hours and have to deal
with that.
I'd suggest using a macro.
If a macro was created, do you know how to implement one?
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Bill Q <ronsmith...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tuesday, November 24, 2015 3:14 PM
Subject: $$Excel-Macros$$ Random Time Problem
I gave this my best shot - but I am not satisfied with it.
PSA.
* What I have are dates between November 15, 2015 to January 16, 2016 * Each
date will have 16 radio "spots".* These spots are to be anywhere between 07:00
to 21:00.
I was able to randomize the time somewhat with the final product in row "H".
However, I see instances where the times between spots are too close for my
liking.
What I need is some sort of random formula that will return a time between
07:00 to 21:00 BUT will never be within 30 minutes of each other.
Anyone ?
Thank you.--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
