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() 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:

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:

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:

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

Share this article

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

Recommended posts

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel

Introduction This article describes how to randomly select 6 unique numbers from 1 to 49. In a 6-from-49 lotto, a […]

Random row from a boolean matrix in excel

Question: It’s the kind of thing I do regularly in matrix oriented languages (APL, K, Gauss etc), but I can’t […]

Create a random playlist

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Create random dates, Mon to Fri, within a year in excel

H.G asks: I need to create lots of random dates within a certain year, for which I simply use =randbetween(). […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Sort a list in random order in excel

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]