# rolling 6 day rotations that should correspond to weekdays of current school calendar year

#### KidlatKulug1

##### New Member
Pls help me create a macro to get columns value from worksheet "Lab Tech" the Times and Day X of rolling 6 days rotation that correspond to current school calendar year (using weekdays only). Start with, If the input date is todays date (11/8/2021) the date falls on day 2 rotation (output are the column TImes and Day 2. Note that the Input and Output is on different worksheet "Master". If the input is on 11/9/2021 day 3 rotation, the output is the Column Times and Day 3, on 11/10/21 is day 4 rotation, and 11/11/21 no rotation since its holiday, continue on 11/12/21 is day 5 rotation, 11/15/21 day 6 rot, 11/16/21 back to day 1 rotation, 11/17/21 day 2 rotation and so on. Note that on holidays there are no Lab day and also following dates there are no Lab day (11/24/21, 12/17-1/3/22, 2/21/22, 3/14/22-3/28/22, 4/15/22, and 5/27/22-7/4/22), which means it will pick up the day of rotation where last left off (ie..12/17/2021-1/3/2022 no lab day so on 1/4/2022 its day 3 rotation and 1/17/22 no lab so 1/18/22 is day 6 rotation). Below is worksheet "Lab Tech".
 LAB Tech TIMES Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 8:45-9:25 Inter MUN ADMIN Inter BAR Inter Van Inter FAK ADMIN 9:30-10:10 ADMIN Campbell Hess Tubolino ADMIN Sears 10:15-10:55 Yontez Scherr ADMIN Mehl ADMIN Valdivia 11:00-11:40 ADMIN Falconer Robertson ADMIN Harris Brekke 11:40-12:30 LUNCH LUNCH LUNCH LUNCH LUNCH LUNCH 12:30-1:10 Lee K RTI K RTI K RTI Contreras Jacobs 1:15-1:55 Lenz Benson Pennington Bailey Frasier Monaco 2:00-2:40 Nothrop Carlson Lauder Rhodes Brandt Gonzales

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### JamesCanale

##### Active Member
No macro for me.
MrExcelPlayground4.xlsx
ABCDEFGHIJKLM
1Base Day 1HolidaysDate:11/8/2021
211/5/202111/24/2021TIMESDay 2TIMESDay 1Day 2Day 3Day 4Day 5Day 6
712/21/202111:40-12:30LUNCH11:40-12:30LUNCHLUNCHLUNCHLUNCHLUNCHLUNCH
812/22/202112:30-1:10K RTI12:30-1:10LeeK RTIK RTIK RTIContrerasJacobs
912/23/20211:15-1:55Benson1:15-1:55LenzBensonPenningtonBaileyFrasierMonaco
1012/24/20212:00-2:40Carlson2:00-2:40NothropCarlsonLauderRhodesBrandtGonzales
1112/25/2021
1212/26/2021
1312/27/2021
1412/28/2021
1512/29/2021
1612/30/2021
1712/31/2021
181/1/2022
191/2/2022
201/3/2022
212/21/2022
223/14/2022
233/15/2022
243/16/2022
253/17/2022
263/18/2022
273/19/2022
283/20/2022
293/21/2022
303/22/2022
313/23/2022
323/24/2022
333/25/2022
343/26/2022
353/27/2022
363/28/2022
374/15/2022
385/27/2022
395/28/2022
405/29/2022
415/30/2022
425/31/2022
436/1/2022
446/2/2022
456/3/2022
466/4/2022
476/5/2022
486/6/2022
496/7/2022
506/8/2022
516/9/2022
526/10/2022
536/11/2022
546/12/2022
556/13/2022
566/14/2022
576/15/2022
586/16/2022
596/17/2022
606/18/2022
616/19/2022
626/20/2022
636/21/2022
646/22/2022
656/23/2022
666/24/2022
676/25/2022
686/26/2022
696/27/2022
706/28/2022
716/29/2022
726/30/2022
737/1/2022
747/2/2022
757/3/2022
767/4/2022
Sheet32
Cell Formulas
RangeFormula
E2:E10E2=INDEX(H2:M10,,MOD(NETWORKDAYS.INTL(A2,E1,1,B2:B76)-1,6)+1)
B4:B20,B39:B76,B23:B36B4=B3+1
Dynamic array formulas.

#### KidlatKulug1

##### New Member
Also I would like to expand the requirement of using input date with rolling 6 day rotation but search for input student name from worksheet "student list" to have the output value from the Day X column. Display the result in worksheet "Master"
 Student Name Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Student 1 PE Band Band PE Band Band Student 2 PE Band Band PE Band Band Student 3 Study Hall Music PE Study Hall Music PE Student 4 PE Band Band PE Band Band Student 5 Study Hall Music PE Study Hall Music PE Student 6 PE Band Band PE Band Band Student 7 PE Music Band Band PE Study Hall Student 8 Study Hall Band PE Music Band PE Student 9 Band Band Band Band PE Band

#### JamesCanale

##### Active Member
MrExcelPlayground4.xlsx
ABCDEFGHIJKLM
1Base Day 1HolidaysDate:11/8/2021
211/5/202111/24/2021TIMESDay 2TIMESDay 1Day 2Day 3Day 4Day 5Day 6
712/21/202111:40-12:30LUNCH11:40-12:30LUNCHLUNCHLUNCHLUNCHLUNCHLUNCH
812/22/202112:30-1:10K RTI12:30-1:10LeeK RTIK RTIK RTIContrerasJacobs
912/23/20211:15-1:55Benson1:15-1:55LenzBensonPenningtonBaileyFrasierMonaco
1012/24/20212:00-2:40Carlson2:00-2:40NothropCarlsonLauderRhodesBrandtGonzales
1112/25/2021Student 4Band
1212/26/2021
1312/27/2021
1412/28/2021
1512/29/2021Student NameDay 1Day 2Day 3Day 4Day 5Day 6
1612/30/2021Student 1PEBandBandPEBandBand
1712/31/2021Student 2PEBandBandPEBandBand
181/1/2022Student 3Study HallMusicPEStudy HallMusicPE
191/2/2022Student 4PEBandBandPEBandBand
201/3/2022Student 5Study HallMusicPEStudy HallMusicPE
212/21/2022Student 6PEBandBandPEBandBand
223/14/2022Student 7PEMusicBandBandPEStudy Hall
233/15/2022Student 8Study HallBandPEMusicBandPE
243/16/2022Student 9BandBandBandBandPEBand
253/17/2022
263/18/2022
273/19/2022
283/20/2022
293/21/2022
303/22/2022
313/23/2022
323/24/2022
333/25/2022
343/26/2022
353/27/2022
363/28/2022
374/15/2022
385/27/2022
395/28/2022
405/29/2022
415/30/2022
425/31/2022
436/1/2022
446/2/2022
456/3/2022
466/4/2022
476/5/2022
486/6/2022
496/7/2022
506/8/2022
516/9/2022
526/10/2022
536/11/2022
546/12/2022
556/13/2022
566/14/2022
576/15/2022
586/16/2022
596/17/2022
606/18/2022
616/19/2022
626/20/2022
636/21/2022
646/22/2022
656/23/2022
666/24/2022
676/25/2022
686/26/2022
696/27/2022
706/28/2022
716/29/2022
726/30/2022
737/1/2022
747/2/2022
757/3/2022
767/4/2022
Sheet32
Cell Formulas
RangeFormula
E2:E10E2=INDEX(H2:M10,,MOD(NETWORKDAYS.INTL(A2,E1,1,B2:B76)-1,6)+1)
E11E11=INDEX(H16:M24,MATCH(D11,G16:G24,0),MOD(NETWORKDAYS.INTL(A2,E1,1,B2:B76)-1,6)+1)
B4:B20,B39:B76,B23:B36B4=B3+1
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
D11List=\$G\$16:\$G\$24

Replies
6
Views
59
Replies
15
Views
118
Replies
0
Views
370
Replies
0
Views
162
Replies
3
Views
234

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,486
Messages
5,770,373
Members
425,612
Latest member
martinijr

### 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.

### Which adblocker are you using?

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

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