Go Back

How to Get Random Times at Specific Intervals in Excel

Read time: 20 minutes

In this tutorial, you will learn how to get random times at specific intervals. Therefore, you need to get familiar with the Excel functions RAND and FLOOR. The idea is to populate a column with random values at specific intervals.

Get random times at specific intervals

Formula using FLOOR and RAND

=FLOOR(RAND(), interval)

First, we will need to know how function RAND works. This is the function without input parameters, which returns random decimal number from the interval between 0 and 1. Every time you press enter, you will get a different value in the cell. The syntax is:

=RAND()

 

Figure 1. RAND function

Now we need to explain FLOOR function. The purpose of this function is to return a number down to the nearest specified multiple. Let’s look at the example of FLOOR function to explain it better. The syntax is:

=FLOOR(number, significance)

=FLOOR(B3, C3)

 

Figure 2. FLOOR function

As you can see in the picture, we want to find the nearest number down from given number in B3 cell, which is divisible by number in C3 cell. In selected row, the result in D3 is 30 because it is the nearest number down from 34 which is divisible by 5.

Now we can explain getting random times at specific times. In our example, we want to generate random times at the interval of 10 minutes in column B, so we will put the following formula in B3 cell and expand it to column B:

=FLOOR(RAND(),"0:10:00")

Figure 3. Random times at specific intervals

The prerequisite for this formula to work is to format all cells in column B as a time. Now the decimal value will automatically be transformed to the time value in the format “HH:MM:SS”. For example, if RAND() returns 0.30, time cell will display 7:12:00 AM.

As we explained in the previous section, FLOOR function will return the time which is nearest to the time returned by the RAND() and divisible by 10 minutes (“0:10:00”). You can see that in our example, in cell B3 the output is 3:50:00 AM. That is the nearest time to the one got by RAND() in the 10 minutes interval.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hello, I am working with a large temperature data set which has data points at certain intervals apart. The time intervals change throughout the data set and I was wondering if there was any way I could bin the values so that they are averaged to every hour as opposed to the random intervals which they are at now? The Date Time data has a (M/D/YY H:M) setup
Solved by E. C. in 11 mins
Given a start and end time I need to call all the data between those two time points from a different sheet. I have to do this over 50 times with different time intervals. All of these 50 times taking place in a different category. I'd like to be able to input which category and the start and end times and be able to automatically output the values at between those times for that specific category.
Solved by B. J. in 20 mins
I have a time column (12:00 AM-11:30 PM it is at 1/2 intervals), I have start and end times for Supervisors. I need to find a formula that will help me find how many Supervisors work at all of the different intervals throughout the day. This would also include overnight. Please and thank you!
Solved by D. L. in 16 mins

Leave a Comment

avatar