One of the nice things about the RANDARRAY function is that it makes it easy to generate a list of random dates. In this worksheet, let’s generate 20 random dates between May 1 and May 30, 2020. Now, to use the RANDARRAY function for this, we’re going to need a max and min value. These values correspond to start and end dates, and I’ve already this set up in cells C4 and C5. In cell C6, I’ll enter 20 since we want 20 dates total. In cell E5, I’ll enter the RANDARRAY function. The rows argument corresponds to the number of dates we want, so I want to pick up the value in cell C6. For columns, we want 1. For min, we want the start date in C4. For max, we want the end date in C5. Finally, because we are creating dates, we want RANDARRAY to generate integers, so the integers argument needs to be set to 1 or TRUE. When I enter the formula, RANDARRAY generates 20 random dates in May. Remember that these values need to be formatted as dates. If I temporarily apply the General number format, you can see that date values in Excel are actually large serial numbers. Now when I change the start or end date, we’ll get a new set of dates. For example, I can easily change the start date to June 1 and the end date to August 31 to generate random dates over a 3-month period. I can also change the number of dates that are created. Now if I add more dates, I’ll need to take care to format the new values as dates. Now, the output from RANDARRAY is not sorted. If I need the dates to be sorted, I can wrap the SORT function around RANDARRAY. Finally, note that RANDARRAY generates dynamic values, not fixed values. As a result, I’ll get a new set of dates each time I make a change to the worksheet. If I need fixed dates, I can copy the dates to the clipboard, and 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.
