Dynamic rotating work schedule

C

Clooney

New Member
Joined
Sep 28, 2012
Messages
41
Office Version
  1. 365
Platform
  1. Windows
I have a pretty tall order here everyone,

I'm trying to make a rotating sanitation list for the remainder of the year for 3 teams.

I want the sheet to auto assign team members job areas (Rooms;columns B,C,D) based on the days of the week the work.

Members of Team 1 can only be assigned any Room Mon-Fri
Members of Team 4 can only be assigned any Room Sat-Tues and are the preferred team for Sundays
Members of Team 5 can only be assigned any Room Wed-Sat and are the preferred team for Saturdays

Each day in column A will have 3 (columns B-D) different employee ID's in each area(Room)

I want the sheet to keep everyone evenly spread out in terms of occurrences and for everyone to work all 3 sets of rooms.

As an added challenge, i would like to be able to add or subtract employee ID's to and from each team and have the sheet auto adjust.

I have a formula in column B that shows what I am trying to do, but it was from another person's work and I having trouble adapting it to my needs. Basically columns B,C,& D need the formulas.

This is the formula in Cell B3 and I just copied it down
Code: 
=IF(OR(WEEKDAY($A2)=1,WEEKDAY($A2)=7),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=5,-5,-5),0),Team1,0),$U$2)+1),$G$2),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=2,-3,-1),0),Team1,0),$U$2)+1),$G$1))


[TABLE="width: 1510"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]Named[/TD]
[TD]Range[/TD]
[TD][/TD]
[TD]Named[/TD]
[TD]Range[/TD]
[TD][/TD]
[TD]Named[/TD]
[TD]Range[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Room 4[/TD]
[TD]Room 5&6[/TD]
[TD]Room 7&8[/TD]
[TD][/TD]
[TD]Gets First Mon-Fri:[/TD]
[TD]LBRMMANI[/TD]
[TD][/TD]
[TD]Team[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Team[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Team[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Team1[/TD]
[TD]Team4[/TD]
[TD]Team5[/TD]
[/TR]
[TR]
[TD]Sunday, July 01, 2018[/TD]
[TD]LBRRFOXW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Gets First Sat/Sun:[/TD]
[TD]LBRRFOXW[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD][/TD]
[TD]Sundays[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD]Saturdays[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Monday, July 02, 2018[/TD]
[TD]LBRBIROB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mon-Fri[/TD]
[TD]LBRRCARD[/TD]
[TD][/TD]
[TD]Sat-Tues[/TD]
[TD]LBRRBURN[/TD]
[TD][/TD]
[TD]Wed-Sat[/TD]
[TD]LBRPLAYT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday, July 03, 2018[/TD]
[TD]LBRJOHNS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRRFOXW[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRKSNOW[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRJCBOU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday, July 04, 2018[/TD]
[TD]LBRJSMIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRBIROB[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRSTHOM[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRMSCRU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday, July 05, 2018[/TD]
[TD]LBRMMANI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRJOHNS[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRACARR[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRTMCCL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday, July 06, 2018[/TD]
[TD]LBRGSTRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRJSMIT[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBREHART[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRJMITC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday, July 07, 2018[/TD]
[TD]LBRBIROB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRMMANI[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRMCOY[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRSKENN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday, July 08, 2018[/TD]
[TD]LBRJOHNS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRGSTRE[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRDHANN[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRPMANS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday, July 09, 2018[/TD]
[TD]LBRJSMIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRRJONE[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRLPING[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRRGEIG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday, July 10, 2018[/TD]
[TD]LBRMMANI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRLDILL[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRCEMBE[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRLBLAK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday, July 11, 2018[/TD]
[TD]LBRGSTRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRJHOWE[/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRLCLIF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday, July 12, 2018[/TD]
[TD]LBRRJONE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LBRNLAZO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday, July 13, 2018[/TD]
[TD]LBRLDILL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I really wish I could upload this sheet for everyone to take a look at.

Thanks for any help I can get on this!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Answered from another forum...works flawlessly!

This proposal makes a few changes to the arrangement of the Team's tables.
This proposal also adds a helper column (E) which may be moved and/or hidden for aesthetic purposes and is populated using:
Code: 
=IF(WEEKDAY(A2,16)=1,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,5),4,5),IF(WEEKDAY(A2,16)=2,IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,4),5,4),IF(WEEKDAY(A2,16)<=4,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,1),4,1),IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,1),5,1))))
The room 4 column is populated using:
Code: 
=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,COUNTIFS(I$5:M$22,E2),MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))),IF(E2=1,1,IF(E2=4,3,5)))
The room 5&6 column is populated using:
Code: 
=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,0,MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2)))+1,IF(E2=1,1,IF(E2=4,3,5)))
The room 7&8 column is populated using:
Code: 
=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=COUNTIFS(I$5:M$22,E2)-1,1,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,0,MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2)))+2),IF(E2=1,1,IF(E2=4,3,5)))
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
332
Eric W
Eric W
A
  • Question Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
498
Anthony86
A
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
92
6StringJazzer
6StringJazzer
D
  • Question Question
Formula to "Review" Alternating Shift Schedules
Replies
0
Views
137
davidcox9
D
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
379
Eric W
Eric W

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top