In this worksheet, let’s generate 20 random times between 7:00 AM and 6:00 PM. To do this, we’ll use the RANDARRAY function. Now, RANDARRAY can generate both integers and decimal values. For Times, we want decimal values, because Excel times are fractional values of one day. For example, 6:00 AM is .25, 12:00 PM is .5, 6:00 PM is .75, and so on. To make it easy to change the Start and End times, I’ve put these values in cells C4 and C5. In cell C6, I’ll enter 20 since we want a total of 20 times. Now I’ll enter the RANDARRAY function in cell E5. The rows argument corresponds to the number of times we want, so this value comes from cell C6. For columns, we want 1. For min, we want the start time in C4. For max, we want the end time in C5. Finally, because we are creating times, we want decimal values, so the integers argument needs to be set to 0 or FALSE. When I enter the formula, RANDARRAY generates 20 random times between 6:00 AM and 7:00 PM. These values must be formatted as times. If I temporarily apply the General number format, you can see that times in Excel are just decimal values. Now I can change the inputs any way I like. For example, I can set the Start time to 8:00 AM and the End time to 5:00 PM, and generate only 12 times. I’ll undo these changes. To sort these times in order, I can wrap the SORT function around the RANDARRAY function. Finally, note that RANDARRAY generates a new set of times each time a change is made to the worksheet. If I need fixed values that don’t change, I can copy the times to the clipboard, then use Paste Special with Values to overwrite the formulas with static values.
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.
