Excluding weekends in a randomly generated non-repeating dates

Question

I am using two formulas to generate random non-repeating values within a specified range.

1st is =IFERROR(RANDBETWEEN($A$1,$A$2),"")

2nd is =IFERROR(LARGE(ROW(INDIRECT(CONCATENATE("$",$A$1,":$",$A$2)))*NOT(COUNTIF($B$1:B1,ROW(INDIRECT(CONCATENATE("$",$A$1,":$",$A$2))))),RANDBETWEEN(1,($A$2+2-$A$1)-ROW(B1))),"")

These two formulas generate a set of 50 values (depending on until where I drag down the 2nd formula) that are unique with each other.

This works fine with dates too. However, I wanted to include an option to exclude weekends. I already made a VBA userform wherein the user could input the start and end date. But I also wish to include a checkbox to whether or not exclude weekends from the range. If doable, I would wish to extend this to give the user the option to exclude days s/he wishes to exclude.

I found the following formula during my googling:

=DATE(2014, 1, 1)+(LARGE(IF(WEEKDAY((DATE(2014, 1, 1)+ROW($1:$365)-1)*((COUNTIF($A$1:A1, DATE(2014, 1, 1)+ROW($1:$365)-1))=0), 2)<6, ROW($1:$365)-1, ""), RANDBETWEEN(1, SUM(--(WEEKDAY((DATE(2014, 1, 1)+ROW($1:$365)-1)*((COUNTIF($A$1:A1, DATE(2014, 1, 1)+ROW($1:$365)-1))=0), 2)<6)))))

However, I can't make it work for me after a few tweaks and tries. Also, it initiates at a start date of 1/1/2014 plus 365 days (this was relatively simple to correct). My only problem really is trying to exclude weekends from the 50 randomly generated non-repeating dates. Incorporating this to the userform is something I can already.

With this, may I ask for your help. Thanks!

I'm pretty sure you cannot get unique, non-repeating, random anything using worksheet functions like randbetween with any reliability. — , Mar 27 '18 at 03:34
@Jeeped yes it is possible and reliable. It may not be the fastest most efficient, but it is possible — Ricardo González, Mar 27 '18 at 03:42
"Random" and "non-repeating" are mutually exclusive. Once you try to exclude repeating numbers you have removed your randomness. — AJD, Mar 27 '18 at 06:34
@AJD, I should've explained the purpose a lot clearer. Anyway, what I have now is the ability to generate 50 non-repeating numbers in a specified range (ex. 1-500) and randomly arrange them from cell A1 to A50. If the range is smaller than 50, it will just arrange these numbers randomly from cell A1 to whatever cell it will reach. What I'm trying to do right now is to exclude weekends if in case my range are dates. — J.Mapz, Mar 27 '18 at 07:06
@J.Mapz: You can use an approach similar to dealing with a deck of cards - have a list of the 500 numbers - randomly select an item from the list (1 - len.list) - take that number from the list and then repeat this fifty times. This is then a list management question instead of a random number question. if you like this idea, I can write it up as an answer later today with more detail (including why it is a better approach). — AJD, Mar 27 '18 at 18:40
@AJD, wow that's a pretty alternative concept of doing it. Gonna try as well as to how to do it. Thanks! — J.Mapz, Mar 27 '18 at 23:59

score 2 · Answer 1 · answered Mar 27 '18 at 03:41

You are looking for =WORKDAY(Arg1, Arg2, Arg3) or you can use it in VBA as WorksheetFunction.WorkDay(Arg1, Arg2, Arg3)

WorksheetFunction.WorkDay Method Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

Arg1 Required Variant Start_date - a date that represents the start date.

Arg2 Required Variant Days - the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

Arg3 Optional Variant Holidays - an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates

Directly from the MS Doc https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheetfunction-workday-method-excel

A good example of how to use this method MS Excel: How to use the WORKDAY Function (WS) https://www.techonthenet.com/excel/formulas/workday.php

Also I recommend reading the article Working with Workdays https://msdn.microsoft.com/en-us/library/aa227592%28v=vs.60%29.aspx

Okay, I'll try playing with the workday; not sure how I could incorporate it to generate random non-repeating, but it'll be a start. Will chat back in a few mins. Thanks! — J.Mapz, Mar 27 '18 at 06:26
@J.Mapz here is a good example of how to generate random non-repeating numbes in an array using vba (accepted answer) https://stackoverflow.com/questions/18543169/unique-random-numbers-using-vba — Ricardo González, Mar 27 '18 at 07:13
@J.Mapz Other accepted answer using cells instead of an Array https://stackoverflow.com/questions/46222822/creating-excel-vba-macro-that-generates-unique-random-numbers — Ricardo González, Mar 27 '18 at 07:16
@J.Mapz and of course, those unique numbers should be the 2nd argument. Third argument can be (a) range(s) of cells too containing Dates for holidays or excluding days. — Ricardo González, Mar 27 '18 at 07:18

score 1 · Answer 2 · 2018-03-27T04:02:30.893

1

This does a reasonable job of generating random Monday through Friday dates for the year 2014.

=WORKDAY.INTL(41640, RANDBETWEEN(0, NETWORKDAYS.INTL(41640, 42004, 1)), 1)

Note that the dates are not completely unique and non-repeating.

I've used the serial date 41,640 for 01-Jan-2014 and 42,004 for 31-Dec-2014 but these could be replaced with any form of true date; e.g. DATE(2014, 1, 1). Both WORKDAY.INTL and NETWORKDAYS.INTL support the optional holiday list parameter.

For truly unique and non-repeating dates, I would use a vba dictionary.

edited Mar 27 '18 at 04:02

answered Mar 27 '18 at 03:57

what about using one of these answers for truly unique and non-repeating numbers (and use them as a second argument). It just requieres some minor tweaks and I saw you also helped in one of the answers :) https://stackoverflow.com/questions/46222822/creating-excel-vba-macro-that-generates-unique-random-numbers – Ricardo González Mar 27 '18 at 07:31
There is a worksheet function method involving a helper column with rand numbers beside a column of all dates then sort on the rands. If vba is to be used, I prefer the dictionary method. – Mar 27 '18 at 07:38

score 0 · Answer 3 · answered Mar 28 '18 at 18:51

An alternative approach is to manage a list instead of trying to manipulate random numbers.

Function MyRandomNumbers() As Collection ' could be anything
    Dim tSourceList As New Collection ' could be a list of anything
    Dim iterator As Long
    ' populate the list - for this example it will only be simple numbers
    For iterator = 1 To 500
        tSourceList.Add iterator ' New list item is the iterator. Could be anything
    Next iterator

    Dim tResultList As New Collection ' the same thing as put in the source list
    Dim index As Long
    For iterator = 1 To 50 ' How ever many numbers you want to pull out of the list
        index = CInt(tSourceList.Count * Rnd()) '<randomly generated number between 0 and length of tSourceList>
        tResultList.Add tSourceList(index)
        tSourceList.Remove (index)
    Next iterator
    Set MyRandomNumbers = tResultList
End Function

Sub testMRN()
Dim v As Collection
Dim r As Variant
    Set v = MyRandomNumbers
    Debug.Print v.Count,
    For Each r In v
        Debug.Print r & " ";
    Next r

End Sub

You can amend the index = part with a better seeded random number.

Excluding weekends in a randomly generated non-repeating dates

3 Answers3