Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Random time?
I am not sure if what i am asking is possible, i have looked around and have not found anything similar.
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 If you have seen my previous question you will understand i am fairly new to Excel, so any explanation and help appreciated. regards slipper |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random time?
"slipper" wrote:
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. |
#3
|
|||
|
|||
Quote:
Thanks for your reply, to make things a little clearer i have a cell displaying a random message selected from a list, this part all works fine. However 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. I am familiar with conditional formatting and how to use it but cannot find a function that does what i need. Is there any fuction that would do as i ask? Excuse my coding in my earlier post it was just my way of making my point clearer and not meant as actual code. If this can be done by Excel formulas that would be great, if it needs 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 slipper |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random time?
"slipper" wrote:
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. "slipper" wrote: 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 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 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! |
#5
|
|||
|
|||
Quote:
I will try and think of some other way to implement what i need. regards slipper |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random time?
"slipper" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time delay refresh on random number | Excel Worksheet Functions | |||
Entering a random date and time in a cell | Excel Discussion (Misc queries) | |||
Excel Charts - Time line for random points on X axis | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) | |||
Various Random Codes not working from time to time | Excel Programming |