• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Distribution of the services of the employees

H

Harry Covair

Member
Hi the forum,


I need your help to solve in VBA a problem that bothers me for some time.

The problem concerns the planning of 4 employees (named from 1 to 4).

Between 02 jan 2014 and 31 dec 2014, for each working day, while one employee begins at 8 am, another stays till 6 pm. The other two employees work normally. In addition, the distribution must be randomly but, based on the 260 working days, the number of times that an employee starts at 8 am or ends at 6 pm must be the same for all.

Any help, ideas, suggestions will be greatly appreciated.

Thanks in advance and Happy New Year to everyone

Harry
(Working on Excel 2010 Win7)
 
Hi Harry ,

I think the problem description could be more detailed.

1. What are the working hours exactly ? You say one employee begins at 8 am , but another stays till 6 pm ; what does this mean ? When does the employee who starts at 8 am end his / her working day ? When does the employee who ends at 6 pm start his / her working day ?

2. What is the meaning of :

The other two employees work normally.

3. Since you are talking of only 4 employees , and the working days are quite a lot , wouldn't the most equal distribution just be a circular roster where the employees go through all the possibilities one after the other ?

For example , if we consider that the number of shifts are S1 , S2 , S3 and S4 , and the employees are E1 , E2 , E3 and E4 , we could just have the cycle :

E1S1 , E2S2 , E3S3 , E4S4 , E1S2 , E2S3 , E3S4 , E4S1 , E1S3 , E2S4 , E3S1 , E4S2 , E1S4 , E2S1 , E3S2 , E4S3

Over 16 working days , all the 4 employees would go through all the 4 shifts.

To get the above distribution into random order , just fill up a range of 260 cells with this sequence of data , and then using a helper column which has random numbers , sort them in ascending or descending order.

Narayan
 
Hi Narayan,
Thanks for your quick answer.

Some more details
I'm working as volunteer for a public library and I realize some little Office developments.

1. What are the working hours exactly ?
The desk is open from 8 am till 6 pm. To keep it simple, as the employees are working 7 hours/day, the one that begins at 8 am leaves the office 7 hours later at 3 pm and the one that finishes at 6 pm begins at 11 am.

2. What is the meaning of :

The other two employees work normally.
they begin at their convenience between 8 am and 11 am and finish 7 hours later.

3. Since you are talking of only 4 employees , and the working days are quite a lot , wouldn't the most equal distribution just be a circular roster where the employees go through all the possibilities one after the other ?

I had forgotten the basic principle: before thinking VBA, first think Excel.
I started in VBA instinctively but your solution seems by far the best. I present it tomorrow monday.
In each case, thanks for your suggestion
Harry
 
You must log in or register to reply here.
Back
Top