"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:

Sampling start date	Sampling end date	1	2	3	4	5
01/10/2017	01/01/2018
01/01/2018	01/04/2018
01/04/2018	01/07/2018
01/07/2018	01/10/2018

<tbody>
</tbody>

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
1	Sampling start date	Sampling end date		1	2	3	4	5
2	1/10/2017	1/1/2018		8/22/2017	7/11/2017	2/14/2017	1/19/2017	1/24/2017
3	1/1/2018	1/4/2018		1/1/2018	1/4/2018	1/2/2018	1/3/2018	#NUM!
4	1/4/2018	1/7/2018		1/4/2018	1/5/2018	#NUM!	#NUM!	#NUM!
5	1/7/2018	1/10/2018		1/8/2018	1/10/2018	1/9/2018	#NUM!	#NUM!
6
7
8
9	Holidays
10	2/19/2018
11	7/3/2018
12	11/11/2018

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula D2 {=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))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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