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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
324
Eric W
Eric W
A
  • Question Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
493
Anthony86
A
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
83
6StringJazzer
6StringJazzer
D
  • Question Question
Formula to "Review" Alternating Shift Schedules
Replies
0
Views
132
davidcox9
D
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
368
Eric W
Eric W

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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