Generate list of random dates with criterion
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
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.
Minor change...I think the array formula in D1 needs to start with "TODAY()+"....
Interesting example...thanks
Thanks!!