which generates a random time at a 15-minute interval. 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 an argument called significance as the rounding multiple, and it recognizes time intervals expressed like this: “0:10” - 10 minutes “0:15” - 15 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: Because Excel can recognize time values, you can get times between 8 AM and 12 PM, with a formula like this: Note: the formula above is general and will work with other numbers too, not just times.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
