Radomize seconds in a timestamp over a undefined range.

All,

I have a column of timestamps (c3: cx) in the following format (h:mm:ss AM/PM).

I would simply like to randomize the seconds section of each stamp via a macro and simply cannot fathom it out...please help!!

Regards,

Skyping

try:

Please Login or Register  to view this content.

Hi,

Try this code :

Please Login or Register  to view this content.

Regards

hi karedog,
it is not needed to initialize pseudo-random generator before each call of Rnd.

Hi Kaper,

It is just a good practice (but probably not needed for this case), as Microsoft says :

Because the Random statement and the Rnd function start with a seed value and generate numbers that fall within a finite range, the results may be predictable by someone who knows the algorithm used to generate them.
http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx

BTW, your code only change the seconds only to 0 or 1, is this expected ?

Regards

You are welcome, glad I can help.

Regards

You can use this code :

Please Login or Register  to view this content.

Regards

@karedog: "code only change the seconds only to 0 or 1, is this expected ?"
So we have a tie here, because your first code (before editing) changed whole day :-D

But it's not the main point to write. I'd like to discuss randomize a bit further.

It is wise to use it, and you probably noticed, that I used it too, but outside of the loop.
But Randomize without parameter uses (I think) system clock as a seed value. So data series generated with periodical initialisation would be "less randomly" distributed than initialized only once.

Have a look into sample file - there are 10000 numbers generated with randomize before each rnd and 10000 just one randomize at the beginning.
Have a look at the difference in frequency of different values generated by both approaches.
I'm not statistician, but anyway densities (frequencies) of numbers generated with repeated randomize are at least surprisingly located.

And it's not just one run result - call test procedure and next (different but again strange) set will be generated. If you examine only a small subsample (say 100) it's not clearly visible. but larger datasets are by no means berrer randomized by repeating randomize ;-).

Originally Posted by skyping

Thank you once again Karedog...work great!!!

You are welcome Skyping, many thanks for the rep. points too.

Originally Posted by Kaper

So we have a tie here, because your first code (before editing) changed whole day :-D

Ok Kaper, it's a draw then mrgreen.gif

But it's not the main point to write. I'd like to discuss randomize a bit further.

It is wise to use it, and you probably noticed, that I used it too, but outside of the loop.
But Randomize without parameter uses (I think) system clock as a seed value. So data series generated with periodical initialisation would be "less randomly" distributed than initialized only once.

Have a look into sample file - there are 10000 numbers generated with randomize before each rnd and 10000 just one randomize at the beginning.
Have a look at the difference in frequency of different values generated by both approaches.
I'm not statistician, but anyway densities (frequencies) of numbers generated with repeated randomize are at least surprisingly located.

And it's not just one run result - call test procedure and next (different but again strange) set will be generated. If you examine only a small subsample (say 100) it's not clearly visible. but larger datasets are by no means berrer randomized by repeating randomize ;-).

Well, I'm not a statistic guy either, so I cannot comment about your work, whether the data is "more random" or "same random" or something else, sorry about this.

Regards

Originally Posted by skyping

I have a column of timestamps (c3: cx) in the following format (h:mm:ss AM/PM). I would simply like to randomize the seconds section of each stamp via a macro

Originally Posted by skyping

how would I randomize the last minute in the above scenario....e.g. h:mm:ss AM/PMh:mm:ss AM/PM goes to h:mX:ss AM/PM ?

You marked this thread as "solved", but none of the suggestions does what you ask. Try the following:

Please Login or Register  to view this content.

The code assumes the cells are formatted as you want.

Originally Posted by Kaper

cells(i,3) = cells(i,3) + rnd/24/60/60

Since Rnd returns a non-integer between 0 and less than 1, this adds random fractional seconds less than 1 instead of whole seconds.

Originally Posted by karedog

cell.Value = cell.Value + (Rnd / 1440)

Originally Posted by karedog

cell.Value = cell.Value + (Int(Rnd * 10) / 1440)

Although this adds fractional minutes (and less than 10 minutes in the second case), which are seconds, the result includes fractional seconds. For example, if Rnd returns 0.1234, Rnd/1440 adds about 7.404 seconds. I suspect "skyping" wants random whole seconds. The fractional seconds might screw up the way he uses the random times.

Moreover, both methods add random time instead of just inserting random seconds. Consequently, they have the potential of changing the minute (minute "decade" in the second case) and hour, which does not seem to be "skyping's" intent.