Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Range of Dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Range of Dates
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." wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Range of Dates
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 On Apr 27, 1:46*pm, Jay wrote: In your example (eg, 5 consecutive days, 1 day, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I generate a list of random dates from dates I specify | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
random dates showing up in userform | Excel Programming | |||
How to generate a random list of weekDAYS between two dates? | Excel Worksheet Functions | |||
How do I generate random numbers using dates in excel? | Excel Worksheet Functions |