Rotating schedule two pools
Hello, I am new to excel and vba. I have successfully created a user form (disclaimer: modifying code I have found on the internet. All credit goes to them.) that auto mailmerges word docs and auto fills pdf from an excel sheet.
I need help with creating a rotating shift in excel. Here is the situation. Where I work there are two pools of employees. Pool "A" and Pool "B". Pool "A" consists of 5 employees and Pool "B" consists of 20 to 25 employees depending on season. Pool "A" is supposed to handle 80% of a job task ( job task will be "JT" from now on) on a Monday through Friday work week. 80% of a 5 day work week is 4 days. Pool "B" is given the JT 1 day a work week. Saturday, Sundays and holidays are supposed to be divided evenly between both Pools with is a total of 25 to 30 employees.
The kicker is that during the work week of Monday through Friday, work needs to be evenly divided between Pool "A" and Pool "B". What I mean is that Monday thru Friday, Pool "A" employees have to work the same, or close to the same, amount of Mondays, Tuesdays, Wednesdays, Thursdays, and Fridays. There are 5 employees so it seems simple at first but Pool "B" is required to work 1 day of a work week. Also, Pool "B" employees work that 1 day a week until all 20 to 25 have worked 1 day a week before it loops back to the 1st employee. It does not matter that Pool "B" employees work an even amount of Monday, Tuesdays, etc.
Saturdays, Sundays, and holidays are supposed to be be evenly distributed by both Pool "A" and Pool "B" employees before they recycle again.
As you can see Pool "A" employees work more JT than Pool "B" employees so ideally it would be nice if each Pool "A" employee gets an 8 day break between their assigned JT. Because there are 20 to 25 Pool "B" employees already get more than 8 days between their assigned JT.
The 8 day breaks and the 80% / 20% distribution rule during the work week of Monday thru Friday is ideal but can be broken to accomodate a fair distribution and keep Pool "A" employees at a minimum of 8 days between JT assignment. Better to go over than under 8 days if possible.
Can anyone help me? Work has been unfairly distributed to Pool "A" employees but management refuses to change anything until they get a better alternative to their winging it.
Is it possible to get a breakdown of how many days each group is getting by day of the week? Could it also be broken down to what percentage of the work week Pool "A" is getting (should be 80%) and what Pool "B" employees are getting? Rather than using names could you use employee number? For Pool "A" it would be 1-5 and for Pool "B" 6-25.
In case you are wondering I am one of the employees of Pool "B" but I used to be a Pool "A" employee so I advocate for them relentlessly. This is just too hard for me to figure out.
Last edited by Noob Coder : November 20th 19 at 12:40 AM