I want to be able to autofill the rest of the rows beginning with row 12 to finish the given sequence in row 6. This a shift rotation schedule that I want to be able to auto fill with a formula to calculate work schedule that will include A shift on days with 4 days on M-TH off F-SUN then back on M-W and then off TH-SUN, and then rotate with B shift on days, 4 on then 3 off, and then 3 on 4 off, and back to A during the day?
| Production Crew Rotating Schedule NEW 2024.xlsx | ||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | |||
| 4 | January | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | ||
| 5 | Day of the week | M | TU | W | TH | F | SA | SU | M | TU | W | TH | F | SA | SU | M | TU | W | TH | F | SA | SU | M | TU | W | TH | F | SA | SU | M | TU | W | ||
| 6 | Day Shift | A | A | A | B | B | B | B | B | B | B | A | A | A | A | A | A | A | ||||||||||||||||
| 7 | Night Shift | B | B | B | A | A | A | A | A | A | A | B | B | B | B | B | B | B | ||||||||||||||||
| 8 | ||||||||||||||||||||||||||||||||||
| 10 | February | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | ||||
| 11 | Day of the week | TH | F | SA | SU | M | TU | W | TH | F | SA | SU | M | TU | W | TH | F | SA | SU | M | TU | W | TH | F | SA | SU | M | TU | W | TH | ||||
| 12 | Day Shift | B | B | B | B | B | B | B | A | A | A | A | A | A | A | |||||||||||||||||||
| 13 | Night Shift | A | A | A | A | A | A | A | B | B | B | B | B | B | B | |||||||||||||||||||
Schedule | ||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4,B10 | B4 | =TEXT(DATE($AH$4,$A4,$C4),"MMMM") |
| C4,C10 | C4 | =DAY(DATE($AH$4,$A$4,1)) |
| D4:AG4 | D4 | =DAY(DATE($AH$4,$A$4,C4+1)) |
| C5:AG5 | C5 | =CHOOSE(WEEKDAY(DATE($AH$4,$A$4,C4)),"SU","M","TU","W","TH","F","SA") |
| C7:AG7,C13:AE13 | C7 | =IF(C6="A","B",IF(C6="B","A","")) |
| D10:AD10 | D10 | =DAY(DATE($AH$4,$A$4,C4+1)) |
| AE10 | AE10 | =IF(AH5="Leap Year",DAY(DATE($AH$4,$A$4,AD4+1)),"") |
| C11:AD11 | C11 | =CHOOSE(WEEKDAY(DATE($AH$4,$A$10,C10)),"SU","M","TU","W","TH","F","SA") |
| AE11 | AE11 | =IF(AE10="","",CHOOSE(WEEKDAY(DATE($AH$4,$A$10,AE10)),"SU","M","TU","W","TH","F","SA")) |
