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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
469
dmt32
D
J
  • Solved
conditional formatting based cells if there are duplicate cells in a range
Replies
9
Views
160
StephenCrump
S
F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
335
Eric W
Eric W
A
  • Question Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
502
Anthony86
A
J
  • Question Question
automatically copying a group of cells if changes are made to the master copy
Replies
3
Views
102
HighAndWilder
H

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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