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
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:
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:
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:
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.