"n" unique random dates between date range, excluding weekends and a list of public holidays

[mafallaize]

Hi all,

This problem has had me stumped for a while now. I'm looking for a formula to produce unique random dates between a date range, excluding weekends and a list of public holidays (different for each year). Like a lottery, once a date has been selected, it shouldn't be used again.

My sheet looks like this:
[TABLE="width: 585"]
<tbody>[TR]
[TD]Sampling start date
[/TD]
[TD]Sampling end date
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2017
[/TD]
[TD="align: right"]01/01/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018
[/TD]
[TD="align: right"]01/04/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2018
[/TD]
[TD="align: right"]01/07/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2018
[/TD]
[TD="align: right"]01/10/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

NETWORKDAYS works well for all of the above requirements, except it creates duplicates.

The Lottery style UDF posted by Jon von der Heyden here could be useful, but I couldn't get it to exclude weekends: https://www.mrexcel.com/forum/excel-questions/638342-formulae-help-please.html

Any ideas would be appreciated!

Thanks
Mark

 

[Eric W]

It can be done in a rather longish complicated formula:

Excel 2012

	A	B	C	D	E	F	G	H
Sampling start date	Sampling end date
Holidays

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/10/2017[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/22/2017[/TD]
[TD="align: right"]7/11/2017[/TD]
[TD="align: right"]2/14/2017[/TD]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]1/24/2017[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]#NUM![/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]1/7/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/4/2018[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/7/2018[/TD]
[TD="align: right"]1/10/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/8/2018[/TD]
[TD="align: right"]1/10/2018[/TD]
[TD="align: right"]1/9/2018[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2/19/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]7/3/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11/11/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=LARGE(IF(WEEKDAY(ROW(INDIRECT($A2&":"&$B2)),2)<6,IF(ISERROR(MATCH(ROW(INDIRECT($A2&":"&$B2)),$A$10:$A$12,0)),IF(ISERROR(MATCH(ROW(INDIRECT($A2&":"&$B2)),$C2:C2,0)),ROW(INDIRECT($A2&":"&$B2))))),RANDBETWEEN(1,NETWORKDAYS($A2,$B2,$A$10:$A$12)-COLUMNS($D2:D2)+1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[mafallaize]

Wow thanks for your quick reply and amazing formula Eric, it works a charm!

Regards
Mark

 

[Eric W]

Glad to help!

FWIW, here's a slightly shorter version:

=LARGE(IF(WORKDAY(ROW(INDIRECT($A2&":"&$B2))-1,1,$A$10:$A$12)=ROW(INDIRECT($A2&":"&$B2)),IF(ISERROR(MATCH(ROW(INDIRECT($A2&":"&$B2)),$C2:C2,0)),ROW(INDIRECT($A2&":"&$B2)))),RANDBETWEEN(1,NETWORKDAYS($A2,$B2,$A$10:$A$12)-COLUMNS($D2:D2)+1))

 

[mafallaize]

Concise is nice! I've used your shorter version. The complexity in this nested formula is remarkable to me.

Thanks again! :D