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

KidlatKulug1

New Member
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
TIMESDay 1Day 2Day 3Day 4Day 5Day 6
8:45-9:25Inter MUNADMINInter BARInter VanInter FAKADMIN
9:30-10:10ADMINCampbellHessTubolinoADMINSears
10:15-10:55YontezScherrADMINMehlADMINValdivia
11:00-11:40ADMINFalconerRobertsonADMINHarrisBrekke
11:40-12:30LUNCHLUNCHLUNCHLUNCHLUNCHLUNCH
12:30-1:10LeeK RTIK RTIK RTIContrerasJacobs
1:15-1:55LenzBensonPenningtonBaileyFrasierMonaco
2:00-2:40NothropCarlsonLauderRhodesBrandtGonzales
 

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
Joined
Jan 13, 2021
Messages
460
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
312/17/20218:45-9:25ADMIN8:45-9:25Inter MUNADMINInter BARInter VanInter FAKADMIN
412/18/20219:30-10:10Campbell9:30-10:10ADMINCampbellHessTubolinoADMINSears
512/19/202110:15-10:55Scherr10:15-10:55YontezScherrADMINMehlADMINValdivia
612/20/202111:00-11:40Falconer11:00-11:40ADMINFalconerRobertsonADMINHarrisBrekke
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
Joined
Nov 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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 NameDay 1Day 2Day 3Day 4Day 5Day 6
Student 1PEBandBandPEBandBand
Student 2PEBandBandPEBandBand
Student 3Study HallMusicPEStudy HallMusicPE
Student 4PEBandBandPEBandBand
Student 5Study HallMusicPEStudy HallMusicPE
Student 6PEBandBandPEBandBand
Student 7PEMusicBandBandPEStudy Hall
Student 8Study HallBandPEMusicBandPE
Student 9BandBandBandBandPEBand
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
460
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
MrExcelPlayground4.xlsx
ABCDEFGHIJKLM
1Base Day 1HolidaysDate:11/8/2021
211/5/202111/24/2021TIMESDay 2TIMESDay 1Day 2Day 3Day 4Day 5Day 6
312/17/20218:45-9:25ADMIN8:45-9:25Inter MUNADMINInter BARInter VanInter FAKADMIN
412/18/20219:30-10:10Campbell9:30-10:10ADMINCampbellHessTubolinoADMINSears
512/19/202110:15-10:55Scherr10:15-10:55YontezScherrADMINMehlADMINValdivia
612/20/202111:00-11:40Falconer11:00-11:40ADMINFalconerRobertsonADMINHarrisBrekke
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
 
Solution
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top