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

*Article last updated on November 30, 2010*

**H.G asks:**

I need to create lots of random dates within a certain year, for which I simply use =randbetween().

However, only Mondays to Fridays are required, no weekend days. How could I possibly adapt a function / formular to bring just those about ?

**Answer:**

** **

### Random dates, Mon to Fri, within a year (duplicates allowed).

Array formula in cell A3:

=DATE(2010, 1, 1)+LARGE(IF(WEEKDAY(DATE(2010, 1, 1)+ROW($1:$365)-1, 2)<6, ROW($1:$365)-1, ""), RANDBETWEEN(1, SUM(--(WEEKDAY(DATE(2010, 1, 1)+ROW($1:$365)-1, 2)<6)))) + CTRL + SHIFT + ENTER. Copy cell A3 and paste it down as far as needed.

### Random unique dates, Mon to Fri, within a year

Array formula in cell E3:

=DATE(2010, 1, 1)+(LARGE(IF(WEEKDAY((DATE(2010, 1, 1)+ROW($1:$365)-1)*((COUNTIF($E$2:E2, DATE(2010, 1, 1)+ROW($1:$365)-1))=0), 2)<6, ROW($1:$365)-1, ""), RANDBETWEEN(1, SUM(--(WEEKDAY((DATE(2010, 1, 1)+ROW($1:$365)-1)*((COUNTIF($E$2:E2, DATE(2010, 1, 1)+ROW($1:$365)-1))=0), 2)<6))))) + CTRL + SHIFT + ENTER. Copy cell E3 and paste it down as far as needed.

This formula is surprisingly slow.

**Download excel sample file for this tutorial.**

** **

** **Generate-random-dates-within a year-with-a-criterion.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**ROW(**reference**)** returns the rownumber of a reference

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

**DATE(**year,month,day**)** returns the number that represents the datein Microsoft Office Excel date-time code

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

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

Lookup two index columns in excel

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

### 4 Responses to “Create random dates, Mon to Fri, within a year in excel”

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

I note you say your formula for "Random unique dates, Mon to Fri, within a year" is quite slow. Here is a UDF (user defined function) solution which appears to work quite quickly. The following is an array-entered** formula that you would use as follows (after first installing the UDF, of course... see below for details how to do this). Select all the cells you want to fill first (the selection must be continguous in a single column), then put this formula in the Formula Bar...

=RndDates(2010)

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Okay, for those to whom UDFs are something new, you would install the UDF by pressing ALT+F11 to go into the VB editor and click Insert/Module from its menu bar, then Copy/Paste the code below into the code window that opened up....

'************************START OF CODE************************

Dim RandomizedYet As Boolean

Function RndDates(Y As Long) As Variant()

Dim X As Long, DaysInYear As Long, Index As Long

Dim RndIndex As Long, Temp As Date, Weekdays() As Variant

' Randomize should only be run one time per session

If Not RandomizedYet Then

RandomizedYet = True

Randomize

End If

' Dimension an array to the exact number of weekdays in the specified year

DaysInYear = DateSerial(Y + 1, 1, 1) - DateSerial(Y, 1, 1)

ReDim Weekdays(1 To DaysInYear)

' Load up that array with all the weekday dates in sequential order

For X = 1 To DaysInYear

If Weekday(DateSerial(Y, 1, X), vbMonday) < 6 Then

Index = Index + 1

Weekdays(Index) = DateSerial(Y, 1, X)

End If

Next

' Since the first Index number of elements of the array

' are dates, truncate the array down to this size

ReDim Preserve Weekdays(1 To Index)

' Now, randomize all the elements in the array of weekday dates

For X = Index To 1 Step -1

RndIndex = Int(X * Rnd + 1)

Temp = Weekdays(RndIndex)

Weekdays(RndIndex) = Weekdays(X)

Weekdays(X) = Temp

Next

' Finally, assign the array of randomized dates as the function's return value

RndDates = WorksheetFunction.Transpose(Weekdays)

End Function

'************************END OF CODE************************

Oh, one thing I forgot to mention... of course your formula is Volatile (changing whenever the sheet its on gets recalculated) whereas, as structured, my UDF is non-Volatile. If, however, you want the displayed values from my UDF to be volatile for some reason, then just add this line to as the first line inside the RndDates function's code (put it after the after the Dim statements though)...

Application.Volatile

Rick Rothstein (MVP - Excel),

Your udf is a lot faster. Thanks for commenting your code.

Thanks for your contribution!

Wow Thanks for the comment with the Code! I am an auditor and you saved me from looking at a calendar to make 40 selections of only weekdays for the samples I need.