Generating Random Times for Events

Generating Random Times for EventsIf you are ever in the need for sample data where the events (sales, support calls, etc.) occur several times throughout any given day, here is a neat little trick to accomplish that. One might think the RANDBETWEEN function, which generates a random integer between two numbers that you feed the function, would do the trick. Well, because RANDBETWEEN only returns whole numbers, the time of the day is always going to be at midnight.

To add increments that are less than one day, you would need to add numbers less than one (i.e. not whole numbers). For example adding 0.25 would take the time to 6 am; 0.5 to noon, and 0.75 6 pm. Luckily, adding a number less than one is easy. The RAND function, which generates a number between 0 and 1, does all of the work you.

To generate a list of random times, then, simply start with whatever date you wish in the first row of your data. All is needed in each cell afterward is the addition of Rand(). After adding Rand() to the previous cell, simply copy (or drag) the formula for as many entries as you wish. See attached for example. If, when you’ve finished the copying/dragging, you don’t see enough entries in each day (only 6 am, 2 pm, and 11:30 pm— 3 entries—for example), simply divide Rand() by 2 (or 3 or 4 or 5), to make the increment each time smaller. Pretty straightforward, huh?

Example: Generating Random Times and Dates


About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

One Response to Generating Random Times for Events

  1. Pingback: What quarter is it? «

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: