scheduling

jerryo1314

Board Regular
Joined
Nov 9, 2002
Messages
50
Hi,
I have a need for scheduling. This would be to schedule parent conferences on a 5 minute bases on a given day. 6th grade IE: Jonny's Mom will see teacher A @ 2:15,teacher B @ 2:30 and teacher C @ 2:45. Now I have 725 little Jonny students to see 60 teachers in 5 minute intervales. Can you get me started scheduling ? It would be nice if there was a yes default incase mom doesn't want to see every teacher.
Jerry
I need a few secrets to get started.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Sorry to have nothing but negative comments, but...

The only secret I can help you with is that what you want to do is extremely complicated in the best of circumstances and given your existing specifications an impossibility. Also, from a logistic and human perspective, the proposed schedule is mind-numbingly tight.

First of all, scheduling is a very complex task. Companies spend thousands to hundreds of thousands of dollars on good scheduling software.

Even that software needs very -- and I mean *very* -- tight specifications. Without much analysis, here are the open issues in your specs.

* You haven't defined which parent must/should meet which teacher. Clearly, it is an impossible task (if not illogical) for a meeting between each parent-teacher combination.

* You haven't allowed time for transitions -- parents moving from one teacher to another, teachers preparing for the next parent.

* You haven't allowed for chance events. One parent taking longer than another to get from one teacher to another. A late arrival. A restroom break.

* You haven't allowed for instances where a parent might want to meet someone particular, say the principal.

Without knowing the logistic, organizational, and political issues involved it is impossible to suggest an alternative. What is clear, at least to me, is that the task on hand is probably best handled without a rigid schedule drawn up by a computer.

I'm curious about one thing. Is this 5 minute meeting with multiple teachers the norm? As a child, my parent(s) met with just one teacher and the principal. As an adult, not having any school-going children, I can't comment on the current norm on parent-teacher meetings. One thing I can say. If I had to meet multiple teachers, each for a lightning interview, I would conclude that the entire exercise is a waste of my time -- other than the political value of "he cares enough about his kid to show up."
 

jerryo1314

Board Regular
Joined
Nov 9, 2002
Messages
50
Tushar,
Welcome to the real world of Middle School Scheduling. I don't write the rules, that is what a teacher is given for a schedule hand written.You have to start somewhere. She/he must be prepared to meet the parent at 5 minutes intervals, and move them on to the next room, that is right next door in most cases.A meeting with a principal is not included, because that is another day if ever. I'm not a teacher but the Admin of network, hoping to put my data base to work and help the 2 secretaries.I have looked into
software and it's to pricey for a school. My budget is $20,000 to maintain or replace 233 Macs and 40 printers. Schools are broke so thought I could get jonnys Mom to see teacher A AT 2:05 teacher
B at 2:10 and so on. Jonny's Mom maynot want to see every teacher, lets say there are 6 teachers in all so thought a yes default and then I could uncheck if a no cell.
thanks for your time,
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
following on from Tushar's remarks re specification etc, I presume that all 700+ pupil's don't need to see all 60 parents. The first task therefore is to break up the students into groups that need to the same set(s) of teachers, schedule the first group, schedule the second, resolve conflicts, schedule the third etc...

I think the the messge to get is that there isn't going to be a magic fix here, and although we might be able to stick some automation / progrmming logic in to help, the success of the exercise is largely going to depend on the amount of preparation / legwork that you put in...

the more of the details / specs you can provide, the more help we might be able to give.

paddy
 

jerryo1314

Board Regular
Joined
Nov 9, 2002
Messages
50

ADVERTISEMENT

Sorry,
Yes you are right let me clean this up or down size it.
There is two time frames, 1:00 pm - 3:30 pm and 6:00 pm - 8:30 pm
Lets work the 1:00 - 3:30 P.M. (suggested ending)using 50 parents to see the 5 main teachers and if we go over the 3:30 time period so be it. This is an organizational schedule if nothing else, not factory automation.
Parent and student name are the same for this exercise, and perhaps some teachers will have to start later than others.
We are using 5 minute intervals.
Have all parents see all 5 teachers for now.
Perhaps work a Y/N later

Now in my sample and it was very simple,I put the
time in column A,
student /parent in column B
teacher #1 header in column C
teacher #2 header in column D
teacher #3 header in column E and color coded the row/cells
and so on , and then I shifted the C column down 1
and the D column down 2 and so on but this should be automatic me hopes.
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Here's my take. In one hour, five teachers can each see 12 parents, as there are twelve five minute time slots. I agree with Tushar's comments, BTW. I am a parent, and I wouldn't bother fronting if only got five minutes. Our little girl is only in grade 1, but we spent 20 minutes with her teacher. Anyway, I've set the spreadsheet up slightly different to yourt proposal, but you would just need to repeat the pattern. See below.
Book1
ABCDEFGHIJKLM
1Parent1.00-1.051.05-1.101.10-1.151.15-1.201.20-1.251.25-1.301.30-1.351.35-1.401.40-1.451.45-1.501.50-1.551.55-2.00
2Parent1teacher1teacher2teacher3teacher4teacher5
3Parent2teacher1teacher2teacher3teacher4teacher5
4Parent3teacher1teacher2teacher3teacher4teacher5
5Parent4teacher1teacher2teacher3teacher4teacher5
6Parent5teacher1teacher2teacher3teacher4teacher5
7Parent6teacher1teacher2teacher3teacher4teacher5
8Parent7teacher1teacher2teacher3teacher4teacher5
9Parent8teacher1teacher2teacher3teacher4teacher5
10Parent9teacher5teacher1teacher2teacher3teacher4
11Parent10teacher4teacher5teacher1teacher2teacher3
12Parent11teacher3teacher4teacher5teacher1teacher2
13Parent12teacher2teacher3teacher4teacher5teacher1
Sheet1


HTH
Richard
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

By the time I read your clarification (and simplification) it was way past my bed time. Similar to Richard's suggestion, here's how I would do it.

But before I share how, keep in mind one thing. About the impossibility bit :) To see 50 parents, a teacher must spend 50*5 or 250 minutes. That's 4 hours and 10 mins. Can't be done in the 1:00 to 3:30 p.m. time slot. Of course, if the 5 teachers saw 50 parents using *both* time periods, it could be done. But, then, you'd have a student-teacher ratio of 10-to-1; that would rival the most 'sought after' schools!

Be that as it may, I would lay out and schedule parents in blocks of 5 (or as many teachers as they typically meet).

Put the names of the students in column A, starting with A2. Put the names of the 5 teachers in row 1 starting with B1.

Select B2,C3,D4,E5,F6 (CTRL-click to select multiple non-contiguous cells). Type 1:00 pm (or 13:00 if using a 24 hour clock). Use CTRL-ENTER rather than just ENTER to complete data entry.

Next, select C2,D3,E4,F5,A6. Enter 1:05 pm.

Basically, enter the same time down the diagonal wrapping around to the first column when you get to the last column.

This guarantees an unique schedule for the first group of student-teacher meetings.

Now repeat for the each subsequent block of 5 (or as many teachers are met by each parent) students using a start time of the previous time + {number of teachers * duration of each meeting}. In this case, it will be 25 mins.

If a particular parent will not meet a particular teacher, leave that slot blank. Don't try to optimize the schedule.

The code below does just that. It works with any number of teachers and any number of students. In the XL VBE, enter it in a standard module of a workbook. Get back to XL and save the workbook as, say, Parent-Teacher-Sched.xls. Create a new workbook and run SchedManager (Tools | Macro > Macros... | 'parent-teacher-sched.xls'!schedManager. It has been tested with combinations of 50 students & 5 teachers, 18 & 6, and 15 & 6.
<pre>
Option Explicit
Function getARng(startCell As Range, direction) As Range
If startCell.Value = "" Then
Set getARng = Nothing
ElseIf startCell.Offset(-1 * CInt(direction = xlDown), _
-1 * CInt(direction = xlToRight)).Value = "" Then
Set getARng = startCell
Else
Set getARng = Range(startCell, startCell.End(direction))
End If
End Function
Function getStartingTime() As Date
On Error Resume Next
Err.Clear
getStartingTime = Application.InputBox("Enter starting time either in 24 hour format (13:00)" _
& vbNewLine & "or with am/pm (1:00 pm)", , "13:00", , , , , 1)
On Error GoTo 0
If Err.Number <> 0 Then
MsgBox "Please enter the time in hh:mm format only"
End If
End Function
Function sameTimeSlots(startCell As Range, _
ByVal NbrTeachers As Integer, _
thisPos As Integer) As Range
Dim rslt As Range, i As Integer
Set rslt = startCell.Offset(0, thisPos)
For i = 1 To NbrTeachers - 1
Set rslt = Union(rslt, startCell.Offset(i, (thisPos + i) Mod NbrTeachers))
Next i
Set sameTimeSlots = rslt
End Function
Sub doOneBlock(ByVal startCell As Range, _
ByVal NbrTeachers As Integer, _
StartTime As Date)
Dim i As Integer
For i = 0 To NbrTeachers - 1
With sameTimeSlots(startCell, NbrTeachers, i)
.Value _
= StartTime + TimeSerial(0, i * 5, 0)
.NumberFormat = "h:mm;@"
End With
Next i
End Sub
Sub doSched(ByVal startCell As Range, ByVal NbrTeachers As Integer, _
NbrStudents As Integer, ByVal StartTime As Date)
Dim i As Integer
For i = 0 To NbrStudents - 1 Step NbrTeachers
doOneBlock startCell.Offset(i, 0), NbrTeachers, StartTime
StartTime = StartTime + TimeSerial(0, NbrTeachers * 5, 0)
Next i
End Sub
Sub schedManager()
Dim TeacherRng As Range, StudentRng As Range, _
vStartTime As Variant, StartTime As Date
Set TeacherRng = getARng(Range("b1"), xlToRight)
Set StudentRng = getARng(Range("a2"), xlDown)
If TeacherRng Is Nothing Or TeacherRng.Cells(TeacherRng.Cells.Count).Column = 256 Then
MsgBox "List of teachers is missing"
Exit Sub
End If
'MsgBox TeacherRng.Address & ", " & StudentRng.Address
StartTime = getStartingTime
'MsgBox StartTime
doSched Range("b2"), TeacherRng.Cells.Count, _
StudentRng.Cells.Count, StartTime
End Sub
</pre>
 

jerryo1314

Board Regular
Joined
Nov 9, 2002
Messages
50
My reqards to both posters. Some food for thought on scheduling.
Let me say this to parents that the lower grades do schedule 15 minutes and if more time is needed then you ask for an after school meeting at some later date.You do have access to your teacher and schools. However if every parent needed 40 or 50 minutes there wouldn't be much teaching done.
By the time your child gets to Middle School you are more interested in his grades than his social fitting.If he/she has other problems then that is a specialist meeting with all his/her teachers and supporting specialists.That is a before school meeting that can run 1 hour or more.
I didn't want readers to think they only have 5 minutes. What makes the 5 minute meeting important is you check up on students to see if he/she has really been going to school and if his grades are real. You would be surprised what Middle School kids can do, or would you, since you yourself were once a Middle School student with big ideas.
thanks you for your time.
Jerry
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
If you have a moment, it would be nice to know if the code I posted provided the required schedule.
 

GeorgeB

Board Regular
Joined
Feb 16, 2002
Messages
239
Tushar
I ran your code and it appears to work perfectly. Nice work. Strange that Jerry would'nt respond after the effort you put into his problem. Have a nice day.
George
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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