## Generate list of random dates with criterion

*Article updated on February 02, 2018*

### 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:

*+ 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:

*+ 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:

*+ 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:

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

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]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 […]### 2 Responses to “Generate list of random dates with criterion”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Minor change...I think the array formula in D1 needs to start with "TODAY()+"....

Interesting example...thanks

Thanks!!