Complicated schedule, can Excel help?

L

LCKern

New Member
Joined
Jul 21, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
We are switching to a new schedule this year, and it's rather complicated. I'm trying to create a syllabus that can work for all of my class periods. Each period meets three times a week according to a schedule, so I'd like to make an excel doc that each kid can use to figure out due dates without having to create six different calendars (which is the only other option I can think of). I've made a video that explains exactly what I want it to do, but I don't know how to do it. Help?!

drive.google.com

Untitled: Jul 21, 2021 11:25 AM.webm

drive.google.com drive.google.com
Thanks in advance!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Hello LCKern, welcome.
If you accept coding as solution and you are familiar with VBA editor code as solution,
you can put this one to the VBA editor.
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Dim vNR As Long, vN As Long
    vNR = Cells(Rows.Count, "A").End(xlUp).Row
   
    Application.ScreenUpdating = False
    Columns("D:E").Interior.ColorIndex = xlNone
    If Target.Cells.Count > 1 Then Exit Sub
    Select Case Target
        Case [B1]
            For vN = 4 To vNR
                If InStr(1, Cells(vN, 3), "/") > 0 Then
                    If Left(Cells(vN, 3), 1) = "B" Then
                        Cells(vN, 4).Interior.ColorIndex = 3
                    Else
                        Cells(vN, 5).Interior.ColorIndex = 3
                    End If
                End If
            Next vN
        Case [C1]
            For vN = 4 To vNR
                If InStr(1, Cells(vN, 3), "/") > 0 Then
                    If Left(Cells(vN, 3), 1) = "G" Then
                        Cells(vN, 4).Interior.ColorIndex = 3
                    Else
                        Cells(vN, 5).Interior.ColorIndex = 3
                    End If
                End If
            Next vN
        Case [D1]
            For vN = 4 To vNR
                If Left(Cells(vN, 3), 1) = "A" Then _
                    Cells(vN, 4).Interior.ColorIndex = 4
                If Right(Cells(vN, 3), 1) = "A" Then _
                    Cells(vN, 5).Interior.ColorIndex = 4
            Next vN
        Case [E1]
            For vN = 4 To vNR
                If InStr(1, Cells(vN, 3), "/") = False Then
                    If Left(Cells(vN, 3), 1) = "B" Then
                        Cells(vN, 4).Interior.ColorIndex = 5
                    Else
                        Cells(vN, 5).Interior.ColorIndex = 5
                    End If
                End If
            Next vN
        Case [F1]
            For vN = 4 To vNR
                If Left(Cells(vN, 3), 1) = "C" Then _
                    Cells(vN, 4).Interior.ColorIndex = 6
                If Right(Cells(vN, 3), 1) = "C" Then _
                    Cells(vN, 5).Interior.ColorIndex = 6
            Next vN
        Case Else
    End Select
   
End Sub
 
Upvote 0
Hi LCKern,

I see @EXCEL MAX has already given you the VBA solution along the lines of your request. I've gone a different direction based on their Class # and your weekly schedule from a dropdown selection in cell B1.
NOTES:
  1. There's no check that the two dates you've entered are Mon & Tue for B/G, Thu & Fri for BC or Wed & Thu for AB, but that could be added as a second highlight if you want.
  2. The table and work area in columns K to Q could be hidden or moved to another sheet.
LCKern.xlsx
ABCDEFGHIJKLMNOPQ
1I'm in Class3
2B/GB/GABC
3TaskUnit/Day #B/G or AB or BCDate 1Date 2ClassMonTueWedThuFri
4Ch. 1 Reading QuizU1 D2B/G2-Aug3-Aug2-Aug12121
5Ch. 2 Reading QuizU1 D4BC5-Aug6-Aug6-Aug3SRT2SRT3
6Ch. 3 Reading QuizU1 D6AB11-Aug12-Aug11-Aug54344
7Ch. 4 Reading QuizU1 D8B/G16-Aug17-Aug16-Aug76565
8 677
9 
Sheet1
Cell Formulas
RangeFormula
K4:K9K4=IFERROR(CHOOSE(MATCH(C4,{"B/G","BC","AB"},0),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH($B$1,$M$4:$M$7,0))-2)),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH($B$1,$P$4:$P$8,0))-2)),INDEX($D4:$E4,,ABS((ISNUMBER(MATCH($B$1,$O$4:$O$8,0))-2)))),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:E999Expression=AND(D4<>"",D4=$K4)textNO
Cells with Data Validation
CellAllowCriteria
B1List1,2,3,4,5,6,7
 
Upvote 0
Thank you both so much! I'll play with these ideas and see what works the way I'm thinking it will! I really appreciate the help!
 
Upvote 0
LCKern said:
Thank you both so much! I'll play with these ideas and see what works the way I'm thinking it will! I really appreciate the help!
Click to expand...
You're welcome!

...and because you specifically said you didn't want to provide 7 different schedules, here's 7 different schedules ;)

LCKern.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2CLASSB/GB/GABC
3TaskUnit/Day #B/G or AB or BCDate 1Date 21234567MonTueWedThuFri
4Ch. 1 Reading QuizU1 D2B/G2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug12121
5Ch. 2 Reading QuizU1 D4BC5-Aug6-Aug6-Aug5-Aug6-Aug5-Aug6-Aug5-Aug5-Aug3SRT2SRT3
6Ch. 3 Reading QuizU1 D6AB11-Aug12-Aug11-Aug11-Aug11-Aug12-Aug11-Aug11-Aug12-Aug54344
7Ch. 4 Reading QuizU1 D8B/G16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug76565
8       677
Sheet1 (2)
Cell Formulas
RangeFormula
G4:M8G4=IFERROR(CHOOSE(MATCH($C4,{"B/G","BC","AB"},0),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH(G$3,$Q$4:$Q$7,0))-2)),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH(G$3,$T$4:$T$8,0))-2)),INDEX($D4:$E4,,ABS((ISNUMBER(MATCH(G$3,$S$4:$S$8,0))-2)))),"")
 
Upvote 0
Solution
As you say this is just idea.
I hope you will be able to modify code to you needs.
 
Upvote 0
...and because I just can't leave well enough alone then here's a version which:
  • Uses a NoClass list on another sheet to highlight any dates which appear on a list of dates when there's no classes.
  • Asks for the Monday date of the week (using Data Validation to ensure it's a Monday) and the class type (B/G, BC or AB) and calculates the first and second dates for that class then displays the dates for classes 1 through 7.

LCKern.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1
2CLASSB/GB/GABC
3TaskUnit/Day #MondayB/G or AB or BCDate 1Date 21234567MonTueWedThuFri
4Ch. 1 Reading QuizU1 D22-AugB/G2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug12121
5Ch. 2 Reading QuizU1 D42-AugBC5-Aug6-Aug6-Aug5-Aug6-Aug5-Aug6-Aug5-Aug5-Aug3SRT2SRT3
6Ch. 3 Reading QuizU1 D69-AugAB11-Aug12-Aug11-Aug11-Aug11-Aug12-Aug11-Aug11-Aug12-Aug54344
7Ch. 4 Reading QuizU1 D816-AugB/G16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug76565
8Geography 126-JulAB28-Jul29-Jul28-Jul28-Jul28-Jul29-Jul28-Jul28-Jul29-Jul677
9Geography 227-DecBC30-Dec31-Dec31-Dec30-Dec31-Dec30-Dec31-Dec30-Dec30-Dec
10Geography 37-FebB/G7-Feb8-Feb7-Feb8-Feb7-Feb8-Feb7-Feb8-Feb7-Feb
11         
12         
Sched
Cell Formulas
RangeFormula
E4:F12E4=IF($C4="","",IFERROR(CHOOSE(MATCH($D4,{"B/G","BC","AB"},0),$C4+COLUMNS($E$3:E$3)-1,$C4+COLUMNS($E$3:E$3)-1+3,$C4+COLUMNS($E$3:E$3)-1+2),""))
H4:N12H4=IFERROR(CHOOSE(MATCH($D4,{"B/G","BC","AB"},0),INDEX($E4:$F4,,ABS(ISNUMBER(MATCH(H$3,$R$4:$R$7,0))-2)),INDEX($E4:$F4,,ABS(ISNUMBER(MATCH(H$3,$U$4:$U$8,0))-2)),INDEX($E4:$F4,,ABS((ISNUMBER(MATCH(H$3,$T$4:$T$8,0))-2)))),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E4:N101Expression=ISNUMBER(MATCH(E4,'No Class'!$A$2:$A$366,0))textNO
Cells with Data Validation
CellAllowCriteria
C4:C12Custom=TEXT(C4,"ddd")="Mon"
D4:D101ListB/G,AB,BC


Sample No Class schedule

LCKern.xlsx
A
1No Class
2Mon, 26-Jul-2021
3Tue, 27-Jul-2021
4Wed, 28-Jul-2021
5Mon, 06-Sep-2021
6Mon, 20-Dec-2021
7Tue, 21-Dec-2021
8Wed, 22-Dec-2021
9Thu, 23-Dec-2021
10Fri, 24-Dec-2021
11Mon, 27-Dec-2021
12Tue, 28-Dec-2021
13Wed, 29-Dec-2021
14Thu, 30-Dec-2021
15Fri, 31-Dec-2021
No Class
 
Upvote 0
Toadstool said:
You're welcome!

...and because you specifically said you didn't want to provide 7 different schedules, here's 7 different schedules ;)

LCKern.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2CLASSB/GB/GABC
3TaskUnit/Day #B/G or AB or BCDate 1Date 21234567MonTueWedThuFri
4Ch. 1 Reading QuizU1 D2B/G2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug12121
5Ch. 2 Reading QuizU1 D4BC5-Aug6-Aug6-Aug5-Aug6-Aug5-Aug6-Aug5-Aug5-Aug3SRT2SRT3
6Ch. 3 Reading QuizU1 D6AB11-Aug12-Aug11-Aug11-Aug11-Aug12-Aug11-Aug11-Aug12-Aug54344
7Ch. 4 Reading QuizU1 D8B/G16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug76565
8       677
Sheet1 (2)
Cell Formulas
RangeFormula
G4:M8G4=IFERROR(CHOOSE(MATCH($C4,{"B/G","BC","AB"},0),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH(G$3,$Q$4:$Q$7,0))-2)),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH(G$3,$T$4:$T$8,0))-2)),INDEX($D4:$E4,,ABS((ISNUMBER(MATCH(G$3,$S$4:$S$8,0))-2)))),"")
Click to expand...

Toadstool said:
You're welcome!

...and because you specifically said you didn't want to provide 7 different schedules, here's 7 different schedules ;)

LCKern.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2CLASSB/GB/GABC
3TaskUnit/Day #B/G or AB or BCDate 1Date 21234567MonTueWedThuFri
4Ch. 1 Reading QuizU1 D2B/G2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug3-Aug2-Aug12121
5Ch. 2 Reading QuizU1 D4BC5-Aug6-Aug6-Aug5-Aug6-Aug5-Aug6-Aug5-Aug5-Aug3SRT2SRT3
6Ch. 3 Reading QuizU1 D6AB11-Aug12-Aug11-Aug11-Aug11-Aug12-Aug11-Aug11-Aug12-Aug54344
7Ch. 4 Reading QuizU1 D8B/G16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug17-Aug16-Aug76565
8       677
Sheet1 (2)
Cell Formulas
RangeFormula
G4:M8G4=IFERROR(CHOOSE(MATCH($C4,{"B/G","BC","AB"},0),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH(G$3,$Q$4:$Q$7,0))-2)),INDEX($D4:$E4,,ABS(ISNUMBER(MATCH(G$3,$T$4:$T$8,0))-2)),INDEX($D4:$E4,,ABS((ISNUMBER(MATCH(G$3,$S$4:$S$8,0))-2)))),"")
Click to expand...

This is the solution I ended up going with. I really appreciate it! My students are just now getting into the rhythm of the schedule and find this quite helpful! Thanks again!
 
Upvote 0
You're welcome and thanks for the feedback.
...and well done for stating the requirement in a video. It made it easier to follow.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Creating self scheduling document, need help autopopulating data
Replies
1
Views
337
Joe4
Joe4
E
  • Question
Dynamic Task Schedule
Replies
4
Views
286
EvenS8245
E
T
  • Question
Help please! Can't figure out how to automatically generate reports and include task description in the generated Excel word doc
Replies
1
Views
242
ttp921
T
S
  • Question
Excel vba that counts days when condition is met and put the schedule in the calendar.
Replies
0
Views
352
saman018
S
S
  • Locked
  • Question
Excel vba that tracks tracks frequency of days.
Replies
1
Views
418
Fluff
Fluff

Forum statistics

Threads
1,215,022
Messages
6,122,721
Members
449,093
Latest member
Mnur

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