Random Range of Dates

[Michael H.]

I'm very fluent with VB / VBA however having some difficulty with the
statistics/logic.

I'm hoping someone here can point me in a direction to solve the
following...

I'm developing a model in excel to run simulations against. One
aspect involves randomly identifying ranges of days/dates within a 365
day calendar year.

Example:

Name Days Vacation
========= ===========
Employee 1 14
Employee 2 28
Employee 3 21

Based on the above I'm trying to generate a yearly work schedule that
identifies/applies random vacation days in the amounts specified in
the list. Ideally I'd like some way of weighting the randomization to
better reflect how vacation is taken (eg, in a 2 week block with an
occasional day here and there, rather than completely random.)

Using the data above as an example, I can easily create a list of 14
random yearly dates for Employee 1. The challenge I'm having creating
logic to randomize into blocks of days (eg, 5 consecutive days, 1 day,
1 day, 5 consecutive days, 3 consecutive days)

If anyone has any ideas/insight into the logic required I'd appreciate
hearing from you.

Thanks in advance.

- Michael

 

[Jay]

In your example (eg, 5 consecutive days, 1 day,

1 day, 5 consecutive days, 3 consecutive days), you may just randomly pick 5 days, then leave the second and third one as is, but use the first one as a base day to get the next consecutive days by add one repeatedly to get the first block days. The only thing maybe if there is an overlap, but you can run another routine to re-assign to get rid of the overlap. -Jay

 

[Michael H.]

My example had a math error due to issues I seem to have with copy/
paste ;-)

Example was meant to imply...

Block 1 = 5 consecutive days
Block 2 = 1 day
Block 3 = 5 consecutive days
Block 4 = 4 consecutive days

Total days = 14

Blocks generated in this fashion would imply Employee 1 is taking
vacation on 4 seperate occasions. (ie, 1 week, 1 day, 1 week, 4
days). For the sake of argument... assumption is weekdays.

Thanks for pointing out the error.

- MH