Automated Multi Section School Time Table in Excel

In this section, we explain the working of Automated Multi Section School Time Table. These involve various collaborations at different levels or levels and teachers ensuring good classroom functioning, avoiding scheduling conflicts, keeping track of teacher preferences and student needs, and accommodating unforeseen circumstances such as teacher absences or class cancellations. Without good organization and tools such as Excel and VBA, this process can be time-consuming, error-prone, and difficult to fix when necessary.

In this session, we have explained in an easy way how we can create an automated school time table in Excel in which there is more than one teacher for one subject or in other words, there is a school with more than one section or shift in which multiple teachers have multiple subjects. Automated Multi Section School Time Table

In this we have implemented this automation system by using VBA code with Excel formula using microsoft excel 2019. below here online video class can watch and do as explain in video.

Step:  Automated Multi Section School Time Table

1. First of all create two excel sheet which name of Data and Timetable.
2. In Data Sheet Create column name as Teacher Name,Subject,Class,Day,Period, Availability and fill entry according your data.
3. create drop down for class column as your section like  10A,10B  and Yes/Nodrop down for Availability column for easy change.(Note:- Create all data sheet column as explain in video)
4. no need to create any entry in Time Table it will auto generate through VBA code.
5. Open VBA Editor and write below code

Sub GenerateTimetable()
Dim wsData As Worksheet
Dim wsTimetable As Worksheet
Dim i As Long
Dim classGrade As String
Dim period As Integer
Dim day As String
Dim subject As String
Dim teacher As String
Dim availability As String
Dim dayColumn As Long
Dim currentRow As Long
Dim classDict As Object
Dim classRow As Long
Dim availableTeacher As String

Set wsData = ThisWorkbook.Sheets("Data")
Set wsTimetable = ThisWorkbook.Sheets("Timetable")

' Clear previous timetable
wsTimetable.Cells.ClearContents

' Create a dictionary to track the starting row for each class
Set classDict = CreateObject("Scripting.Dictionary")

' Iterate through input data
For i = 2 To wsData.Cells(Rows.Count, 1).End(xlUp).row
classGrade = wsData.Cells(i, 3).Value
period = wsData.Cells(i, 5).Value
day = wsData.Cells(i, 4).Value
subject = wsData.Cells(i, 2).Value
teacher = wsData.Cells(i, 1).Value
availability = wsData.Cells(i, 6).Value

' Determine the column for the day
Select Case day
Case "Monday"
dayColumn = 2
Case "Tuesday"
dayColumn = 3
Case "Wednesday"
dayColumn = 4
Case "Thursday"
dayColumn = 5
Case "Friday"
dayColumn = 6
Case "Saturday"
dayColumn = 7
End Select

' If class is not in the dictionary, add it and set up headers
If Not classDict.exists(classGrade) Then
If classDict.Count = 0 Then
classRow = 1
Else
classRow = classRow + 12 ' Adjust row number for next class
End If
classDict.Add classGrade, classRow

' Set headers for the new class timetable
wsTimetable.Cells(classRow, 1).Value = "Class: " & classGrade
wsTimetable.Cells(classRow + 1, 1).Value = "Period"
wsTimetable.Cells(classRow + 1, 2).Value = "Monday"
wsTimetable.Cells(classRow + 1, 3).Value = "Tuesday"
wsTimetable.Cells(classRow + 1, 4).Value = "Wednesday"
wsTimetable.Cells(classRow + 1, 5).Value = "Thursday"
wsTimetable.Cells(classRow + 1, 6).Value = "Friday"
wsTimetable.Cells(classRow + 1, 7).Value = "Saturday"

' Format headers
With wsTimetable.Range(wsTimetable.Cells(classRow + 1, 1), wsTimetable.Cells(classRow + 1, 7))
.Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
End With
End If

' Insert period if not already there
currentRow = classDict(classGrade) + period + 1
wsTimetable.Cells(currentRow, 1).Value = period

' Check if the teacher is available
If availability = "N" Then
' Find any available teacher for the period
availableTeacher = FindAnyAvailableTeacher(wsData, day, period)
If availableTeacher <> "" Then
teacher = availableTeacher
availability = "Y" ' Update availability to indicate a teacher is found
End If
End If

' Insert subject and teacher
If availability = "Y" Then
wsTimetable.Cells(currentRow, dayColumn).Value = subject & " (" & teacher & ")"
Else
wsTimetable.Cells(currentRow, dayColumn).Value = subject & " (No available teacher)"
End If

' Apply border to the inserted cells
With wsTimetable.Range(wsTimetable.Cells(currentRow, 1), wsTimetable.Cells(currentRow, 7))
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
End With
Next i

MsgBox "Timetable generated successfully!"
End Sub

Function FindAnyAvailableTeacher(ws As Worksheet, day As String, period As Integer) As String
Dim i As Long
Dim teacher As String

' Iterate through input data to find any available teacher
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).row
' Check if the teacher is available for the specified day and period
If IsTeacherFree(ws, ws.Cells(i, 1).Value, day, period) Then
teacher = ws.Cells(i, 1).Value
FindAnyAvailableTeacher = teacher
Exit Function
End If
Next i

' If no teacher found for the specified day and period,
' return an empty string to indicate that no available teacher is found
FindAnyAvailableTeacher = ""
End Function

Function IsTeacherFree(ws As Worksheet, teacher As String, day As String, period As Integer) As Boolean
Dim i As Long

' Check if the teacher is free for the specified day and period
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).row
If ws.Cells(i, 1).Value = teacher And ws.Cells(i, 4).Value = day And ws.Cells(i, 5).Value = period Then
IsTeacherFree = False
Exit Function
End If
Next i

IsTeacherFree = True
End Function

After that save this code as macro enabled and assign macro button on Time Table Sheet for update record.

Conclusion Automated Multi Section School Time Table

Using this technique can automated you school teacher class time table easily with multiple subject with multiple teacher. Automated Multi Section School Time Table

Daywise Automated School Time Table

Check Playlist for more video classes