Dynamic rotating work schedule excel

Question

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 =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))

The teams are set up in 3 separate columns using named ranges. I am not sure how to type the above formula to include all three teams and also get the formula to use Team 4 on only Saturday-Tuesday, Team 5 on only Wednesday-Saturday and Team 1 only on Monday-Friday.

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

Thanks for any help I can get on this!

Your post sounds like you're soliciting someone to write the VBA macro for you. We're a help community and would love to help you learn how to code this yourself. Break it down into smaller steps and start your own macro, then come back and ask specific questions when you get stuck. — PeterT
Im not asking for VBA help, the formula I have is so close to what I need. — Chris Hall

Chris Hall Chris Hall · Accepted Answer · 2018-07-09T16:27:41

Answered from another forum and works perfect..no need for VBA

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: =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:

=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:

=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:

=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)))

Dynamic rotating work schedule excel

1 Answers