Exceljet

Quick, clean, and to the point

Random times at specific intervals

Excel formula: Random times at specific intervals
Generic formula 
=FLOOR(RAND(),"0:15")
Explanation 

To generate random times in at specific intervals you can use the RAND function with the FLOOR function. In the example shown, the formula in B6 is:

=FLOOR(RAND(),"0:15")

which generates a random time at a 15-minute interval.

How this formula works

The RAND function generates a decimal number between zero and 1. So, you might get output like this from RAND() in three cells:

0.54739314
0.919767722
0.633760119

Dates in Excel are defined as simple numbers, where 1 = 1 day. This means you can simply divide 1 by the decimal value of time to get a value that corresponds to time as Excel sees it, for example:

1/12 = 12 hours = .5 days
1/6 = 6 hours = .25 days
1/8 = 8 hours = .333 days

This means we can use RAND() to generate a decimal value between 1 and 0, then round that number down with FLOOR to the nearest interval. FLOOR takes a argument called "significance" as the rounding multiple, and it recognizes time intervals expressed like this:

"0:10" - 15 minutes
"0:15" - 10 minutes
"0:30" - 30 minutes
"0:45" - 45 minutes

Setting and upper and lower time

If you want to limit the hours used by RAND, you can use this general formula force RAND to output a number between an upper and lower value:

=RAND()*(upper-lower)+lower

Because Excel can recognize time values, you can get times between 8 AM and 12 PM, with a formula like this:

=RAND()*("12:00"-"8:00")+"8:00"

Note: the formula above is general and will work with other numbers too, not just times.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.