My Schedule: Am I dreaming?

First, I'm sorry to have written a novel. But from what I've seen so far I
guess the planning stages are the most important, and if someone can offer
assistance, they need as much information as possible.



I want to create a schedule that automatically updates information in a
table based on information from various worksheets. Curently, this is all
done by hand for all grades and classes.



Basic info:



My school has class 22 day a month; Monday through Friday and the 2nd and
4th Saturday.



Each weekday has six periods, Saturdays have four.



There are three different grades; some grades have class one time a week,
others have class two times a week.



Grades that meet once a week have 27 classes; grades that meet twice a week
have 54 classes.



I've just started out and all I have made is a worksheet with the dates in
COL A and the class periods (1-6) in COL B-G.



In each row beside the date, I've put the proper class in the corresponding
class period.



I'd like the table to look something like a schedule; Sunday-Saturday across
the top and periods 1-6 in a column under each day. The information I need
in the table is what class number will be taught. They need to be counted in
sequence an the totals have to carry from month to month.



Example:



On Monday 1st period the H2A class has a class; so



April week 1, Monday - period 1 shows H2A Class 1

April week 2, Monday - period 1 shows H2A Class 2

April week 3, Monday - period 1 shows H2A Class 3

April week 4, Monday - HOLIDAY 1 shows H2A no class

May week 1, Monday - period 1 shows H2A Class 4

May week 2, Monday - SCHEDULE CHANGE period 1 (shows J1B Class 6 instead)

May week 3, Monday - period 1 shows H2A Class 5

May week 4, Monday - period 1 shows H2A Class 6



To make matters more complicated, because Saturday classes are only twice a
month, they are rotated into the schedule.



Sometimes Wednesday period 1-4 is replaced with Saturday period 1-4.

Sometimes Thursday period 5-6 is replaced with Saturday period 1-2, and
Friday period 5-6 is replaces with Saturday period 3-4



So, I need the cells in the schedule table to report the information this
way; they need to count all the times a class occurs to date and display the
count in a cell for a given day on a given period. Simply counting all the
instances that 'H2A' appears in the Monday, first period cell doesn't work,
because a schedule change may shift that class to another day or period.



So here's the question: can any one suggest a format for the worksheet
besides the one I mentioned above? Since I plan to make a pivot table to
display the information should I break it down a little more to make it
easier to build the table? I.e. Should I add another column for month and
week?



Anyway, I guess the first task is to get the basic schedule worksheet set up
properly. Second is to build a table to display the info (along with
formulas to get the info?) If I can get that far, just to have the class,
e.g., H2A to display in a cell, along with what number class it is that
would be great. After that, I'd like to get information from another table
that says what the contents of the class are, print, review, test,
presentation, etc.

Hi JEM - I did something like this for a company schedule
but I'm not an MVP just a user.

As I understand it the variables are the class, the
module taught, the date it's taught and the period it's
taught. What are you trying to count and why? (i.e. are
you trying to make sure each class get enough classes,
each module is taught, or what) -- I kinda got lost on
the counting part.

Ian.

-----Original Message-----




First, I'm sorry to have written a novel. But from what
I've seen so far I
guess the planning stages are the most important, and if
someone can offer
assistance, they need as much information as possible.



I want to create a schedule that automatically updates
information in a
table based on information from various worksheets.
Curently, this is all
done by hand for all grades and classes.



Basic info:



My school has class 22 day a month; Monday through
Friday and the 2nd and
4th Saturday.



Each weekday has six periods, Saturdays have four.



There are three different grades; some grades have class
one time a week,
others have class two times a week.



Grades that meet once a week have 27 classes; grades
that meet twice a week
have 54 classes.



I've just started out and all I have made is a worksheet
with the dates in
COL A and the class periods (1-6) in COL B-G.



In each row beside the date, I've put the proper class
in the corresponding
class period.



I'd like the table to look something like a schedule;
Sunday-Saturday across
the top and periods 1-6 in a column under each day. The
information I need
in the table is what class number will be taught. They
need to be counted in
sequence an the totals have to carry from month to month.



Example:



On Monday 1st period the H2A class has a class; so



April week 1, Monday - period 1 shows H2A Class 1

April week 2, Monday - period 1 shows H2A Class 2

April week 3, Monday - period 1 shows H2A Class 3

April week 4, Monday - HOLIDAY 1 shows H2A no class

May week 1, Monday - period 1 shows H2A Class 4

May week 2, Monday - SCHEDULE CHANGE period 1 (shows J1B
Class 6 instead)

May week 3, Monday - period 1 shows H2A Class 5

May week 4, Monday - period 1 shows H2A Class 6



To make matters more complicated, because Saturday
classes are only twice a
month, they are rotated into the schedule.



Sometimes Wednesday period 1-4 is replaced with Saturday
period 1-4.

Sometimes Thursday period 5-6 is replaced with Saturday
period 1-2, and
Friday period 5-6 is replaces with Saturday period 3-4



So, I need the cells in the schedule table to report the
information this
way; they need to count all the times a class occurs to
date and display the
count in a cell for a given day on a given period.
Simply counting all the
instances that 'H2A' appears in the Monday, first period
cell doesn't work,
because a schedule change may shift that class to
another day or period.



So here's the question: can any one suggest a format for
the worksheet
besides the one I mentioned above? Since I plan to make
a pivot table to
display the information should I break it down a little
more to make it
easier to build the table? I.e. Should I add another
column for month and
week?



Anyway, I guess the first task is to get the basic
schedule worksheet set up
properly. Second is to build a table to display the info
(along with
formulas to get the info?) If I can get that far, just
to have the class,
e.g., H2A to display in a cell, along with what number
class it is that
would be great. After that, I'd like to get information
from another table
that says what the contents of the class are, print,
review, test,
presentation, etc.






.

A reply to my post! (smile!) I never expected it, I stopped watching this
one a while ago. Hope you are still out there.



Anyway, to answer your question:



'As I understand it the variables are the class, the module taught, the date
it's taught and the period it's taught. What are you trying to count and
why? (i.e. are you trying to make sure each class get enough classes, each
module is taught, or what) I kinda got lost on the counting part.'



If the schedule remained static it wouldn't be a problem to track the
classes and modules, but the schedule is always in a state of flux. That is,
we get the basic schedule for the year with all that national holiday and
events blacked out and start from there. Based on the Monday though Saturday
schedule we have we can manually count the classes and figure out the
modules. However, periodically (every week or ten days) there is a schedule
change. For example, before a testing period, the headmaster discovers some
classes will not have enough class periods before the exam so the classes
get switched around, Thursday 1-4 changes to Saturday 1-4 and Thursday 5-6
becomes Monday 1-2, etc. it gets really confusing. So when one class
receives and extra period or loses a period all classes modules that occur
after that have to be changed (+1 or -1 class module) for the entire year.
Then, a week or ten days later, if we receive another schedule change the
schedule has to be adjusted again. I have 5 JHS classes that meet twice a
week and 7 SHS classes that meet once a week. In just one month, I've had to
recount the classes six or seven times. It's mind boggling. (For example,
three of my J1 classes meet on Monday, so I teach lesson 1, then each one of
them is taught again on Tuesday, Wednesday, and Thursday, so I teach lesson
2, but let's say there is no Tuesday class. The following Monday, I teach
lesson 3 to two classes and lesson 2 to the classes that didn't have class
the previous Tuesday.



I'll leave it at that for now, if you see this post and can catch what I'm
trying to do let me know. Also, I'd be happy to post more information or
links to show you sme examples of what I'm trying to do.



Thanks!

"Ian" wrote in message
...
Hi JEM - I did something like this for a company schedule
but I'm not an MVP just a user.

As I understand it the variables are the class, the
module taught, the date it's taught and the period it's
taught. What are you trying to count and why? (i.e. are
you trying to make sure each class get enough classes,
each module is taught, or what) -- I kinda got lost on
the counting part.

Ian.

-----Original Message-----




First, I'm sorry to have written a novel. But from what
I've seen so far I
guess the planning stages are the most important, and if
someone can offer
assistance, they need as much information as possible.



I want to create a schedule that automatically updates
information in a
table based on information from various worksheets.
Curently, this is all
done by hand for all grades and classes.



Basic info:



My school has class 22 day a month; Monday through
Friday and the 2nd and
4th Saturday.



Each weekday has six periods, Saturdays have four.



There are three different grades; some grades have class
one time a week,
others have class two times a week.



Grades that meet once a week have 27 classes; grades
that meet twice a week
have 54 classes.



I've just started out and all I have made is a worksheet
with the dates in
COL A and the class periods (1-6) in COL B-G.



In each row beside the date, I've put the proper class
in the corresponding
class period.



I'd like the table to look something like a schedule;
Sunday-Saturday across
the top and periods 1-6 in a column under each day. The
information I need
in the table is what class number will be taught. They
need to be counted in
sequence an the totals have to carry from month to month.



Example:



On Monday 1st period the H2A class has a class; so



April week 1, Monday - period 1 shows H2A Class 1

April week 2, Monday - period 1 shows H2A Class 2

April week 3, Monday - period 1 shows H2A Class 3

April week 4, Monday - HOLIDAY 1 shows H2A no class

May week 1, Monday - period 1 shows H2A Class 4

May week 2, Monday - SCHEDULE CHANGE period 1 (shows J1B
Class 6 instead)

May week 3, Monday - period 1 shows H2A Class 5

May week 4, Monday - period 1 shows H2A Class 6



To make matters more complicated, because Saturday
classes are only twice a
month, they are rotated into the schedule.



Sometimes Wednesday period 1-4 is replaced with Saturday
period 1-4.

Sometimes Thursday period 5-6 is replaced with Saturday
period 1-2, and
Friday period 5-6 is replaces with Saturday period 3-4



So, I need the cells in the schedule table to report the
information this
way; they need to count all the times a class occurs to
date and display the
count in a cell for a given day on a given period.
Simply counting all the
instances that 'H2A' appears in the Monday, first period
cell doesn't work,
because a schedule change may shift that class to
another day or period.



So here's the question: can any one suggest a format for
the worksheet
besides the one I mentioned above? Since I plan to make
a pivot table to
display the information should I break it down a little
more to make it
easier to build the table? I.e. Should I add another
column for month and
week?



Anyway, I guess the first task is to get the basic
schedule worksheet set up
properly. Second is to build a table to display the info
(along with
formulas to get the info?) If I can get that far, just
to have the class,
e.g., H2A to display in a cell, along with what number
class it is that
would be great. After that, I'd like to get information
from another table
that says what the contents of the class are, print,
review, test,
presentation, etc.






.