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:
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:
To generate random integers between two numbers, you can use the RANDBETWEEN function. In the example shown, the formula in B5 is: = RANDBETWEEN ( 1 , 100 ) This formula is then copied down from B5 to B11. The result is random numbers between 1-100...
To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. In the example shown, the formula in B5 is: = RANDBETWEEN ( DATE ( 2016 , 1 , 1 ), DATE ( 2016 , 12 , 31 )) This formula is then...
To quickly fill a range of cells with random text values, you can use a formula based on the CHOOSE and RANDBETWEEN functions. In the example shown, the formula in C5 is: = CHOOSE ( RANDBETWEEN ( 1 , 4 ), "Red" , "Blue" , "...
In this video, we'll look at a few ways to generate random values with the RANDBETWEEN function, including random dates, random prices, and random workdays.
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.