Generate random dates each month with no disernible pattern

[bsweet0us]

Sorry for the confusing title, but not sure how else to word it. What I'm looking to accomplish is the following....

At my work, we have to audit an employee's cash drawer on a monthly basis. To prevent each employee from knowing when each audit will occur, the selected dates have to be totally at random with no obvious pattern (i.e. always the same day of the week or the same time of the month).

I can generate a list of random dates within a specific date range, but I would need to add in the variables of not matching the day from the previous month or the week from the previous month. If I can get that base, I can figure out how to add more exclusions if necessary.

I'm not opposed to VBA, so feel free to include that in your thought process as well.

This place has never let me down before, and I'm sure that trend will continue.

THANKS IN ADVANCE!

 

[StephenCrump]

To prevent each employee from knowing when each audit will occur, the selected dates have to be totally at random with no obvious pattern (i.e. always the same day of the week or the same time of the month).

If the dates are random, you will necessarily get repeats of the same day/week. Picking 20th October and 20th November is just as random as 20th October and 5th November, or 31st October and 1 November.

Can you provide more information:

- What are months - are we talking standard calendar months?
- Do you need adjustment for working days/public holidays/part-timers?
- How do you define same day?
- How do you define same week?

 

[bsweet0us]

If the dates are random, you will necessarily get repeats of the same day/week. Picking 20th October and 20th November is just as random as 20th October and 5th November, or 31st October and 1 November.

Can you provide more information:

- What are months - are we talking standard calendar months?
- Do you need adjustment for working days/public holidays/part-timers?
- How do you define same day?
- How do you define same week?

Calendar months, only regular work days (excluding holidays). Same day is same weekday. Same week would be the first Tuesday or third Thursday, if that makes sense.
I understand random will result in repeats, but that's what I'm trying to avoid if possible.

Thanks for looking into this!

 

[shg]

Suggestion:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Audit Dates​

[/td][td="bgcolor:#F3F3F3"]

Workdays Apart​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

Mon 02 Jan 2017​

[/td][td][/td][td]A2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#E5E5E5"]

Wed 08 Mar 2017​

[/td][td="bgcolor:#E5E5E5"]

48​

[/td][td]A3: =WORKDAY(A2, MAX(1, NORM.INV(RAND(), 22, 10)))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#E5E5E5"]

Thu 30 Mar 2017​

[/td][td="bgcolor:#E5E5E5"]

17​

[/td][td]B3: =NETWORKDAYS(A2, A3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td="bgcolor:#E5E5E5"]

Thu 27 Apr 2017​

[/td][td="bgcolor:#E5E5E5"]

21​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#E5E5E5"]

Thu 01 Jun 2017​

[/td][td="bgcolor:#E5E5E5"]

26​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#E5E5E5"]

Mon 17 Jul 2017​

[/td][td="bgcolor:#E5E5E5"]

33​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td="bgcolor:#E5E5E5"]

Tue 29 Aug 2017​

[/td][td="bgcolor:#E5E5E5"]

32​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#E5E5E5"]

Tue 03 Oct 2017​

[/td][td="bgcolor:#E5E5E5"]

26​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td="bgcolor:#E5E5E5"]

Wed 11 Oct 2017​

[/td][td="bgcolor:#E5E5E5"]

7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]

Mon 23 Oct 2017​

[/td][td="bgcolor:#E5E5E5"]

9​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#E5E5E5"]

Thu 09 Nov 2017​

[/td][td="bgcolor:#E5E5E5"]

14​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td="bgcolor:#E5E5E5"]

Fri 24 Nov 2017​

[/td][td="bgcolor:#E5E5E5"]

12​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#E5E5E5"]

Thu 30 Nov 2017​

[/td][td="bgcolor:#E5E5E5"]

5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td="bgcolor:#E5E5E5"]

Tue 06 Feb 2018​

[/td][td="bgcolor:#E5E5E5"]

49​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td="bgcolor:#E5E5E5"]

Fri 09 Feb 2018​

[/td][td="bgcolor:#E5E5E5"]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td="bgcolor:#E5E5E5"]

Fri 16 Mar 2018​

[/td][td="bgcolor:#E5E5E5"]

26​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td="bgcolor:#E5E5E5"]

Wed 25 Apr 2018​

[/td][td="bgcolor:#E5E5E5"]

29​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td="bgcolor:#E5E5E5"]

Fri 18 May 2018​

[/td][td="bgcolor:#E5E5E5"]

18​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td="bgcolor:#E5E5E5"]

Wed 27 Jun 2018​

[/td][td="bgcolor:#E5E5E5"]

29​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td="bgcolor:#E5E5E5"]

Mon 23 Jul 2018​

[/td][td="bgcolor:#E5E5E5"]

19​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td="bgcolor:#E5E5E5"]

Thu 09 Aug 2018​

[/td][td="bgcolor:#E5E5E5"]

14​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td="bgcolor:#E5E5E5"]

Wed 19 Sep 2018​

[/td][td="bgcolor:#E5E5E5"]

30​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td="bgcolor:#E5E5E5"]

Mon 05 Nov 2018​

[/td][td="bgcolor:#E5E5E5"]

34​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td="bgcolor:#E5E5E5"]

Thu 22 Nov 2018​

[/td][td="bgcolor:#E5E5E5"]

14​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td="bgcolor:#E5E5E5"]

Tue 01 Jan 2019​

[/td][td="bgcolor:#E5E5E5"]

29​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td="bgcolor:#E5E5E5"]

Thu 07 Feb 2019​

[/td][td="bgcolor:#E5E5E5"]

28​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td="bgcolor:#E5E5E5"]

Thu 14 Mar 2019​

[/td][td="bgcolor:#E5E5E5"]

26​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td="bgcolor:#E5E5E5"]

Thu 16 May 2019​

[/td][td="bgcolor:#E5E5E5"]

46​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td="bgcolor:#E5E5E5"]

Thu 30 May 2019​

[/td][td="bgcolor:#E5E5E5"]

11​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td="bgcolor:#E5E5E5"]

Mon 24 Jun 2019​

[/td][td="bgcolor:#E5E5E5"]

18​

[/td][td][/td][/tr]
[/table]

 

[StephenCrump]

A3: =WORKDAY(A2, MAX(1, NORM.INV(RAND(), 22, 10)))

That works for me!

Or perhaps it's an impossible task? https://en.wikipedia.org/wiki/Unexpected_hanging_paradox

 

[shg]

Or perhaps it's an impossible task? https://en.wikipedia.org/wiki/Unexpected_hanging_paradox

I expected that was going to show up

 

[StephenCrump]

I expected that was going to show up

... hence your suggested solution, which nicely sidesteps the end of month constraint.