## Generate list of random dates with criterion

*Article last 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

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

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 […]

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 […]

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

Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]

### 2 Responses to “Generate list of random dates with criterion”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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

Interesting example...thanks

Thanks!!