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

1. Rick Rothstein (MVP - Excel) says:

   November 28, 2010 at 9:34 pm

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

   Reply
2. Rick Rothstein (MVP - Excel) says:

   November 28, 2010 at 9:43 pm

   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

   Reply
3. Oscar says:

   November 29, 2010 at 9:08 am

   Rick Rothstein (MVP - Excel),

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

   Thanks for your contribution!

   Reply
4. Bahamian Auditor says:

   December 1, 2011 at 9:26 pm

   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.

   Reply