Random time?

[slipper]

I am not sure if what i am asking is possible, i have looked around an
have not found anything similar.
I want a random time to be generated between 0 and 15 minutes, and fo
this to be added to time now, when this new time is reached i would lik
the contents of a cell to be displayed.

So something like

new time = NOW() + RANDBETWEEN(0,15) ------> store value

when time = new time(stored value) then display cell contents


If you have seen my previous question you will understand i am fairl
new to Excel, so any explanation and help appreciated.


regards

slippe

 

[joeu2004]

I want a random time to be generated between 0 and 15 minutes,
and for this to be added to time now, when this new time is
reached i would like the contents of a cell to be displayed.

So something like
new time = NOW() + RANDBETWEEN(0,15) ------> store value
when time = new time(stored value) then display cell contents

Display how? Display which cell contents?

RANDBETWEEN() is an Excel function. Are you writing an Excel formula or VBA
code?

In Excel:

=NOW() + TIME(0,RANDBETWEEN(0,15),0)

But that is not too useful for your purposes, as I understand them
(vaguely).

In VBA:

Dim newTime As Double
Static didRandomize As Boolean
If Not didRandomize Then Randomize : didRandomized = True

newTime = Now() + TimeSerial(0,Int(16*Rnd()),0) ' note: 16, not 15
or
newTime = Now() + TimeSerial(0,Evaluate("randbetween(0,15)"),0)

Application.OnTime newTime,"mySub"

What you do in "mySub" depends on the answers to my questions.

 

[slipper]

I want a random time to be generated between 0 and 15 minutes,
and for this to be added to time now, when this new time is
reached i would like the contents of a cell to be displayed.

So something like
new time = NOW() + RANDBETWEEN(0,15) ------> store value
when time = new time(stored value) then display cell contents-

Display how? Display which cell contents?

RANDBETWEEN() is an Excel function. Are you writing an Excel formula o
VBA
code?

In Excel:

=NOW() + TIME(0,RANDBETWEEN(0,15),0)

But that is not too useful for your purposes, as I understand them
(vaguely).

In VBA:

Dim newTime As Double
Static didRandomize As Boolean
If Not didRandomize Then Randomize : didRandomized = True

newTime = Now() + TimeSerial(0,Int(16*Rnd()),0) ' note: 16, not 15
or
newTime = Now() + TimeSerial(0,Evaluate("randbetween(0,15)"),0)

Application.OnTime newTime,"mySub"

What you do in "mySub" depends on the answers to my questions.

Thanks for your reply, to make things a little clearer i have a cel
displaying a random message selected from a list, this part all work
fine.

However for several of the messages i want them hidden from the user fo
a random time (0-15 minutes).

I can hide the message from the user by originally filling the cel
black which effectivley hides the message, the problem is how to clea
this formatting after the random time has elapsed.

I am familiar with conditional formatting and how to use it but canno
find a function that does what i need.

Is there any fuction that would do as i ask? Excuse my coding in m
earlier post it was just my way of making my point clearer and not mean
as actual code.

If this can be done by Excel formulas that would be great, if it need
VBA coding i would need a walkthrough as i have not used it before.


The line of code you posted

=NOW() + TIME(0,RANDBETWEEN(0,15),0)

would that work in conditional formatting as a new rule?

Thanks for any help


slippe

 

[joeu2004]

The line of code you posted
=NOW() + TIME(0,RANDBETWEEN(0,15),0)
would that work in conditional formatting as a new rule?

NOW()+TIME(...) simply computes a number, a future date&time. It does not
cause an event to take place at the future time.

You might calculate the future date&time in some cell (X1). (How to do that
is another matter.) Then for the cell with the hidden message, you might
have the conditional formats:

=NOW()>=X1
and
=NOW()<X1

each specifying an appropriate cell font and/or fill color.

However, NOW() is not recalculated automagically. Excel does not
re-evaluate NOW() every second, for example. So unless there is a
recalculation at the date&time in X1, the cell format will not change then.

Moreover, there is the matter of how the future date&time is entered into
X1. You cannot simply use NOW()+TIME(...) because NOW() changes
dynamically. It is the current time, after all. Also, RANDBETWEEN()
changes dynamically, as well. It is recalculated every time there is a
recalculation cycle; for example, every time any cell in any worksheet in
the workbook is edited.

So you would have to use VBA code either to effectively compute
NOW()+TIME(...) or to enable the recalculation of X1, if X1 is on a
worksheet for which calculation is normally disabled.

And since VBA is needed anyway, I would forego any attempt to make
conditional formatting work for this purpose. Instead, I would use VBA to
implement all of the needed functionality: perhaps an event procedure and
one or two procedures that are executed using Application.OnTime.

for several of the messages i want them hidden from the user
for a random time (0-15 minutes).

I can hide the message from the user by originally filling the
cell black which effectivley hides the message, the problem is
how to clear this formatting after the random time has elapsed.

This is very complicated and difficult to do correctly. It is difficult to
cover and handle all contingencies.

In addition to the problem of revealing the hidden, there is the question
of: once revealed, must the cell fill format be restored to black (hidden)
again? If so, what triggers that?

There is no single "right answer". You're the inventor: you need to
specify the process and the actions precisely enough to implement.

And BTW, I do not believe setting a black background color truly hides the
message (when the foreground color is black). It is still visible in the
Formula Bar, next to "fx" under the Tool Bar, for example. Unless you have
removed the Formula Bar, and you have taken steps to prevent the user from
reinstating it.

So, I think we need to hear a lot more details about your current or
intended design before we start providing solutions.

And it might help if you provided an example Excel file that demonstrates
the intended design, as well as you can do, of course.

I think excelbanter.com provides a method for attaching files. But more
generally, upload the example Excel file to a file-sharing website, and post
the "shared" URL in a response here. The following is a list of some free
file-sharing websites; or use one of your own choice.

Box.Net: http://www.box.net/files
Dropbox: http://dropbox.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Windows Live Skydrive: http://skydrive.live.com

Unfortunately, I will not be able to help you further. This will take a lot
of work, and I do not have that kind of time.

But hopefully, my comments above are helpful by putting you on the right
path. And perhaps someone else will jump in to step you through the
implementation, once you provide all of the design details.

Good luck!

 

[slipper]

The line of code you posted
=NOW() + TIME(0,RANDBETWEEN(0,15),0)
would that work in conditional formatting as a new rule?-

NOW()+TIME(...) simply computes a number, a future date&time. It doe
not
cause an event to take place at the future time.

You might calculate the future date&time in some cell (X1). (How to d
that
is another matter.) Then for the cell with the hidden message, yo
might
have the conditional formats:

=NOW()>=X1
and
=NOW()<X1

each specifying an appropriate cell font and/or fill color.

However, NOW() is not recalculated automagically. Excel does not
re-evaluate NOW() every second, for example. So unless there is a
recalculation at the date&time in X1, the cell format will not chang
then.

Moreover, there is the matter of how the future date&time is entere
into
X1. You cannot simply use NOW()+TIME(...) because NOW() changes
dynamically. It is the current time, after all. Also, RANDBETWEEN()
changes dynamically, as well. It is recalculated every time there is

recalculation cycle; for example, every time any cell in any workshee
in
the workbook is edited.

So you would have to use VBA code either to effectively compute
NOW()+TIME(...) or to enable the recalculation of X1, if X1 is on a
worksheet for which calculation is normally disabled.

And since VBA is needed anyway, I would forego any attempt to make
conditional formatting work for this purpose. Instead, I would use VB
to
implement all of the needed functionality: perhaps an event procedur
and
one or two procedures that are executed using Application.OnTime.

for several of the messages i want them hidden from the user
for a random time (0-15 minutes).

I can hide the message from the user by originally filling the
cell black which effectivley hides the message, the problem is
how to clear this formatting after the random time has elapsed.-

This is very complicated and difficult to do correctly. It is difficul
to
cover and handle all contingencies.

In addition to the problem of revealing the hidden, there is th
question
of: once revealed, must the cell fill format be restored to blac
(hidden)
again? If so, what triggers that?

There is no single "right answer". You're the inventor: you need to
specify the process and the actions precisely enough to implement.

And BTW, I do not believe setting a black background color truly hide
the
message (when the foreground color is black). It is still visible i
the
Formula Bar, next to "fx" under the Tool Bar, for example. Unless yo
have
removed the Formula Bar, and you have taken steps to prevent the use
from
reinstating it.

So, I think we need to hear a lot more details about your current or
intended design before we start providing solutions.

And it might help if you provided an example Excel file tha
demonstrates
the intended design, as well as you can do, of course.

I think excelbanter.com provides a method for attaching files. But mor

generally, upload the example Excel file to a file-sharing website, an
post
the "shared" URL in a response here. The following is a list of som
free
file-sharing websites; or use one of your own choice.

Box.Net: http://www.box.net/files
Dropbox: http://dropbox.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com
Windows Live Skydrive: http://skydrive.live.com

Unfortunately, I will not be able to help you further. This will take
lot
of work, and I do not have that kind of time.

But hopefully, my comments above are helpful by putting you on the righ

path. And perhaps someone else will jump in to step you through the
implementation, once you provide all of the design details.

Good luck!

Ok thanks mate for your input and taking the time to reply in length to
my query. Back to the drawing board i guess.

I will try and think of some other way to implement what i need.

regards

slipper

 

[joeu2004]

Ok thanks mate for your input and taking the time to reply
in length to my query. Back to the drawing board i guess.
I will try and think of some other way to implement what i need.

Download https://app.box.com/s/g14vbenkl79uv4s3llym and see if that gets you
started.

Note: Ignore any preview errors and simply download the file. box.net
preview is limited.

I hope the comments in the textbox in Sheet1 are somewhat self-explanatory.

I tried to guess what you might be trying to do. At least, it has similar
elements: select a message randomly; select a random timeout for some
messages; display those as black until the timeout elapses.

I tried to mimick how I thought you might implement that functionality. It
is not the way I would do it. I would rely more VBA code.

I also implemented a countdown cell. If you do not want that, I'll tell you
how to eliminate it.

My timeout is in seconds, not minutes. I don't have the patience to wait
more than a few seconds. Simply change TimeSerial(0,0,1) to
TimeSerial(0,x,0).

After downloading and opening the Excel file, press alt-F11 to open the VBA
window and study the VBA procedures. You should see the Project Explorer
pane on the left. If not, press ctrl+R to open the Project Explorer pane.

Double-click on Sheet1 to see the Worksheet_Change event procedure. That
schedules the "countdown" procedure, if conditions are right. (See comments
in the VBA code.)

If you do not want the countdown cell, change TimeSerial(0,0,1) to
TimeSerial(0,0,x), and eliminate the statement.

Double-click on Module1 to see the procedure "countdown", as well as the
function myRandBetween.

If you do not want the countdown cell, change the statement x = x-1 to
simply x = 0. That is in addition to the change in Worksheet_Change, noted
above.

Those are minimal changes. Of course, there are more efficient
modifications that you could make.

Hope that helps.