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

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

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 […]### 4 Responses to “Create random dates, Mon to Fri, within a year in excel”

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

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.