Noob Coder
New Member
- Joined
- Nov 21, 2019
- Messages
- 3
Is there a formula that would automate a rotating schedule in the following format.
I have two ranges named Group A and Group B. The Group A range has 5 employees and Group B has 21 employees.
Monday through Friday 1 employee from Group A should work 4 days and 1 employee from Group B should work 1 day on a rotating schedule weekly. Here is an example:
MON: Group A / employee 1
TUE: Group A / employee 2
WED: Group A / employee 3
THUR: Group A / employee 4
FRI: Group B / employee 1
Then next week it would like like this:
MON: Group A / employee 5
TUE: Group B / employee 4
WED: Group A / employee 2
THUR: Group A / employee 3
FRI: Group A / employee 4
This rotation would continue for 52 weeks. Eventually all the Group B employees would work 1 day until they have all gone then start again.
Ideally it would be nice if each employee from Group A had 8 days between their next scheduled work day. The weekends and holidays is where the problems start.
The weekends and holidays need to be evenly spread to both Group A and Group B employees while also keeping the weekday rotation going. Basically Group A has to handle 80% of Monday thru Friday with the weekends and holidays being spread evenly over both groups. This is where my brain is pettering out. The holidays are: Wed Jan1, Mon Jan 20, Mon Feb 17, Mon May 25, Fri Jul 3, Mon Sep 7, Mon Oct 12, Wed Nov 11, Thur Nov 26, Fri Dec 25.
The numbers of 80% of Monday thru Friday covered by Group A and spreading evenly the weekends and holidays is best case scenario. Any chance someone could help with a forunla for this?
I have an excel with 1 worksheet for January and planned to duplicate the worksheet for the rest of the months but I am stuck on figuring out the formula. I have very limited vba and excel experience. I have done some vba. I am using my phone to post this but will gladly email my excel. I can't seem to upload it here.
I have two ranges named Group A and Group B. The Group A range has 5 employees and Group B has 21 employees.
Monday through Friday 1 employee from Group A should work 4 days and 1 employee from Group B should work 1 day on a rotating schedule weekly. Here is an example:
MON: Group A / employee 1
TUE: Group A / employee 2
WED: Group A / employee 3
THUR: Group A / employee 4
FRI: Group B / employee 1
Then next week it would like like this:
MON: Group A / employee 5
TUE: Group B / employee 4
WED: Group A / employee 2
THUR: Group A / employee 3
FRI: Group A / employee 4
This rotation would continue for 52 weeks. Eventually all the Group B employees would work 1 day until they have all gone then start again.
Ideally it would be nice if each employee from Group A had 8 days between their next scheduled work day. The weekends and holidays is where the problems start.
The weekends and holidays need to be evenly spread to both Group A and Group B employees while also keeping the weekday rotation going. Basically Group A has to handle 80% of Monday thru Friday with the weekends and holidays being spread evenly over both groups. This is where my brain is pettering out. The holidays are: Wed Jan1, Mon Jan 20, Mon Feb 17, Mon May 25, Fri Jul 3, Mon Sep 7, Mon Oct 12, Wed Nov 11, Thur Nov 26, Fri Dec 25.
The numbers of 80% of Monday thru Friday covered by Group A and spreading evenly the weekends and holidays is best case scenario. Any chance someone could help with a forunla for this?
I have an excel with 1 worksheet for January and planned to duplicate the worksheet for the rest of the months but I am stuck on figuring out the formula. I have very limited vba and excel experience. I have done some vba. I am using my phone to post this but will gladly email my excel. I can't seem to upload it here.