Overview

Today we are going to build an array formula. Step by step creating random dates with a criterion. The criterion is to generate random weekdays within a specified interval. From today and ten days forward.

We are going to learn how to:

  • Create an array of dates
  • Filter dates with a criterion
  • Create a list of random dates

Create an array of dates

In cell A1, type:

=TODAY()+ROW(1:11)-1 + CTRL + SHIFT + ENTER

TODAY() returns the current date.

ROW(1:11)-1 creates an array of numbers. {0,1,2,3,4,5,6,7,8,9,10}.

2-Nov-2010 + {0,1,2,3,4,5,6,7,8,9,10}

equals

{2-Nov-2010, 3-Nov-2010,4-Nov-2010,5-Nov-2010,6-Nov-2010,7-Nov-2010,8-Nov-2010,9-Nov-2010,10-Nov-2010,11-Nov-2010,12-Nov-2010}

Select cell range A1:A12. Click Formula bar. Press CTRL + SHIFT + ENTER. Each cell now have a single date from the array. See picture above, cell range A1:A11.

Filter dates with a criterion

The weekday function helps us identify the day of the week of a date. In this example we are going to create a list of random weekdays, not weekend days (saturdays and sundays).

Weekday function returns a number from 1 to 7. Monday=1, Tuesday=2, Wednesday=3, Thursday=4, Fridays=5, Saturday=6 and sunday=7)

WEEKDAY(array, 2)<6 is our criterion. All dates in the array returning a number less than 6 is a weekday.

The array formula in cell B1:

=WEEKDAY(TODAY()+ROW($1:$11)-1, 2)<6 + CTRL + SHIFT + ENTER

Select cell range B1:B11. Click Formula bar. Press CTRL + SHIFT + ENTER. Cell range B1:B11 now contains TRUE or FALSE.

Create a list of random dates

If WEEKDAY(TODAY()+ROW($1:$11)-1, 2)<6 then create a new array from 0 to 10.

Array formula in C1:

=IF(WEEKDAY(TODAY()+ROW($1:$11)-1, 2)<6, ROW(1:11)-1, "") + CTRL + SHIFT + ENTER

Select C2:C12 and click formula bar. Press CTRL + SHIFT + ENTER.

As you can see in cell range C1:C11 the returning array is {0, 1, 2, 3, , , 6, 7, 8, 9, 10}

With Large function we can randomly select a number in this array: {0, 1, 2, 3, , , 6, 7, 8, 9, 10}

Example,

Large({0, 1, 2, 3, , , 6, 7, 8, 9, 10}, 1) equals 10.
Large({0, 1, 2, 3, , , 6, 7, 8, 9, 10}, 9) equals 0.

Randbetween function returns a random number between the numbers you specify. In this example the numbers are 1 and 9.

The question is how do we calculate the number we need to specify?

WEEKDAY(TODAY()+ROW($1:$11)-1, 2)<6 returns {TRUE,  TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}

Excel can´t sum that array so using double negations converts the array to: {1, 1, 1, 0 ,0 ,1 ,1 ,1 ,1 ,1 ,1}

SUM(--(WEEKDAY(TODAY()+ROW($1:$11)-1, 2)<6)) equals 9

Array Formula in cell D1:

=TODAY()+LARGE(IF(WEEKDAY(TODAY()+ROW($1:$11)-1, 2)<6, ROW(1:11)-1, ""),RANDBETWEEN(1, SUM(--(WEEKDAY(TODAY()+ROW($1:$11)-1, 2)<6))) + CTRL + SHIFT + ENTER

Copy cell D1 and paste it to cell range D2:D11. See picture below.

Download excel sample file for this tutorial.
Generate random dates with a criterion.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

WEEKDAY(serialnumber, [return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

LARGE(array,k) returns the k-th largest row number in this data set

RANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify