PDA

View Full Version : generate work schedule



pipsmultan
05-18-2015, 09:38 AM
Respectable Greetings to all :)

I am running into big problem and challenge

I have two sheets called “Timetable” sheet & “Teachers Details” sheet. what i am trying to accomplish is to generate auto Timetable Randomly, but i m confuse and not getting any idea + solution how it will done..

Two sheets in my excel file are

1. Teachers Details (Sheet).
2. Timetable (Sheet)

plz see the attach excel sheet and image

Now i am trying to achieve is when anyone enter the detail of a teacher in Teachers Details (Sheet) with the following credential like (which subjects he can teach, which classes he can teach, and in which periods). Excel auto generate timetable from the following given details.

1. Teaching Subject
2. Teachers Name
3. Enter Teaching Class
4. Enter Period (Optional)


i dont know will “VB/Macro Code” will do it

ANY help from any one will be great help and highly appreciated

Best Regards

Yongle
05-18-2015, 10:23 AM
Can you please clarify what the rules are in respect of split classes:

To illustrate, and looking specifically at the extract below, In the 2nd period 10thA have one teacher, but 10thB have three teachers for different subjects

13429

So the first question is
- when is it permissible to split a class into more than one group?
which is the same as asking
- which subjects are allowed to "clash" and is it the same rule for each "teaching class"

The second question is
Is there always ONE entry in the timetable for each permitted "subject"/"teacher"/"teaching class" combination?
(eg (teacher)Sir William will teach (subject)math once to (teaching class)9thB)

pipsmultan
05-18-2015, 11:07 PM
Can you please clarify what the rules are in respect of split classes:

To illustrate, and looking specifically at the extract below, In the 2nd period 10thA have one teacher, but 10thB have three teachers for different subjects

So the first question is
- when is it permissible to split a class into more than one group?
which is the same as asking
- which subjects are allowed to "clash" and is it the same rule for each "teaching class"

The second question is
Is there always ONE entry in the timetable for each permitted "subject"/"teacher"/"teaching class" combination?
(eg (teacher)Sir William will teach (subject)math once to (teaching class)9thB)


thanks a lot for your reply :)

Q1: when is it permissible to split a class into more than one group? which is the same as asking
Ans: it is permissible to split a class into more than one group "on two conditions"
1. 1st condition is when Students hav option to choose subjects of their choice from given group
e.g if any student want to study Science he can choose Any of One subject from (Biology, Computer, Electric Wiring) but class will conduct same time with their different respective subject teachers in different rooms.

that’s why in the 2nd period 10th-A have one teacher, but 10th-B have three teachers for different subjects (2nd period)

Q2: which subjects are allowed to "clash" and is it the same rule for each "teaching class"
Ans: Subjects are allowed to "clash" when we are shortage of teachers than we have to merge the classrooms of same subjects, like sometime we will merge Electric Wiring students of two classes (10th B & 9th B Teacher "Sir James" teaching two classes at a time.) in one Room with one Electric Wiring Teacher, so that students will not go free without class. (But this will not always the case with "Electric Wiring" subject it can be with any subjects)

Q3: Is there always ONE entry in the timetable for each permitted "subject"/"teacher"/"teaching class" combination? (eg (teacher)Sir William will teach (subject)math once to (teaching class)9thB)
Ans: Yes, only if subject are of same type (e.g Math and G.Math) Sir William teaching two group of students (Math and G.Math) in same room in same 7th period.

plz find the excel sheet
13437


i hope i am able to make you understandable

plz feel free to ask as many confusion as you hav, bcz i know timetable think is very challenging, bcz i m dealing with it :)

thanks for taking time in my timetable issues, highly appreciating

High Regards

Yongle
05-19-2015, 03:07 AM
Thanks for previous answers - all make perfect sense.
I have made good progess but there are still a few things that I need to understand so that I can arrive at a generic model that can deal with all the variations

Next questions (see image)

1 Is there an option Physics OR Science ( Period1)?
2 Option is Biology OR Computer OR Electric Wiring (per your previous post) - so why is Period2 Biology/Comp/Math ?
3 Why are there 2 teachers in Period3?
4 Why is biology not included in Period6
5 Is there an option Chemistry OR Science (Period8)

thanks







13438

pipsmultan
05-20-2015, 01:52 AM
1 Is there an option Physics OR Science ( Period1)?
2 Option is Biology OR Computer OR Electric Wiring (per your previous post) - so why is Period2 Biology/Comp/Math ?
3 Why are there 2 teachers in Period3?
4 Why is biology not included in Period6
5 Is there an option Chemistry OR Science (Period8)


You are more than welcome. and thanks for appreciating

Sorry for late reply.

Q1. Is there an option Physics OR Science ( Period1)?
Ans: No, there is no option in Physics OR Science ( Period1). mistakenly typed

Q2. Option is Biology OR Computer OR Electric Wiring (per your previous post) - so why is Period2 Biology/Comp/Math ?
Ans: Yes, Option is (Biology OR Computer OR Electric Wiring) as per my previous post. It is mistakenly typed Biology/Comp/Math.

Q3 Why are there 2 teachers in Period3?
Ans: The 2nd teacher is teacher assistant (Helper teacher), to help and manage the class activates. (that's why Sometime we put 2 teacher in one class)

Q4 Why is biology not included in Period6
Ans: Again typing mistake in timetable. (i have forgot to included biology) :(

Q5 Is there an option Chemistry OR Science (Period8)
Ans: No.

now you can see the a lots of mistakes done when we are making timetable manually. :)

for your good understanding i have Attached the EXCEL Sheet only "3 Class course of academic studies" so that you can understand the Compulsory and Optional Subjects and Subjects Groups
13443

i m very mush delighted, the time and effort you r putting in. :) :)

If you have any questions, please feel free to ask as many as yo can :)

bundle of thanks :)

High Regards

Yongle
05-20-2015, 04:39 AM
Thank you for your answers and the syllabus. I have some questions about the syllabus

Compulsory Subjects
Islamiat / Ethics (Choose any 1 Course) - are both subjects taught by same teacher?
MATHS / G.MATHS (Choose any 1 Course) - both are taught by same teacher - per your previous reply

if subject are of same type (e.g Math and G.Math) Sir William teaching two group of students (Math and G.Math) in same room in same 7th period.


Science Group
Per syllabus option = Biology / Computer (Choose any 1 Course) - Has this now changed to Biology OR Computer OR Electric Wiring? (per your previous post)
Humanities Group
There is an option - Choose any 2 Course from Following... - there are 22 different subjects - a big choice!
Do some teachers teach several of these subjects?
Do they teach more than one of the subjects in the same Period?
Why do most of these subjects not appear in the actual Timetable?

The Timetable
The timetable you provided has 8 periods for 4 classes. This is the timetable for ONE day - is the timetable the SAME every day of the week?

After I receive these answers, I will send you a summary of what the Generic Model will do, and you can then tell me if that is exactly what you want. When we agree the model, then the code will be written.

Yongle

Yongle
05-20-2015, 09:58 AM
I will be busily occupied on other business for the next 2 days

In the meantime can you reply to my questions in post#06 and have a look at the attached plan of attack

thanks

SamT
05-20-2015, 01:34 PM
I think in terms of VBA rather than Excel

Attached is very rough draft of how I would design the Workbook.

Sheets Courses and Teachers are static lookup tables.

Sheets Class Schedules and Teacher Schedules are filled out using a UserForm with code for all the scheduling validations and constraits. They become distributable to teachers, bulletin boards, students et al.


The administrater opens the (Syllabus) Form and select a class (Class 8th). A new form shows showing the Cousres in Class 8th. He or she pick a couse and a new Form loads showing the course, the periods taught, and the teachers assigned to each Period with blnks for unassigned. Whenever the Admin clicks on an empty "Teacher Assigned" box a dropdon apeares with all the teachers qualified and available. the Form can also show any restrictions some teacher may have.

When she is done with the Teacher Assignment Form, he returns to the Class Form to select another course. Or Goes back to the Syllabus form to chose another Class to work on.

Yongle
05-20-2015, 02:15 PM
Hi pipsmultan (http://www.vbaexpress.com/forum/member.php?54282-pipsmultan)

I notice @SamT has posted a possible solution for you. Please look at it and see if you think it will match your requirements. If you have any questions about it please put @SamT at the beginning of your reply

I will look at Sam's solution AFTER I have posted mine (it is more fun that way) . If you are happy to use that solution, that is fine. But I would appreciate the answers to my questions in order that I might complete my solution for you. Please reply to my questions with @yongle at beginning of your reply.

thanks

SamT
05-20-2015, 03:03 PM
Hey!

It's just an idea for the two of you to think about. I get big ones once in a while

Ideas that is. If I just had a big brain to go with the ideas, the world would be a wonderful place.

pipsmultan
05-20-2015, 09:23 PM
I will be busily occupied on other business for the next 2 days

In the meantime can you reply to my questions in post#06 and have a look at the attached plan of attack

thanks

I have just schedule a meeting with my course coordinator head’s today , bcz I'm sure i m also missing a some of information here to. I will be in a meeting with them and i discuss all the questions & issues we are having to design a perfect timetable and also discuss your beautiful Generic Model of timetable :).

I will get back to you after the meeting and will answer all the questions with in 24hurs. I am pretty sure we will be generating best timetable with your great effort. :)

Best Regards and Good luck and Wishes for Success in Business.

pipsmultan
05-20-2015, 09:30 PM
I think in terms of VBA rather than Excel

Attached is very rough draft of how I would design the Workbook.

thanks "SamT" for the rough draft. your input is highly appreciating. i will discussed the draft with my timetable coordinator and heads, and get back you to all nice people :)

thanks alot :)

High Regards

pipsmultan
05-25-2015, 04:01 AM
I hope this reply finds you great in health and spirits.




Compulsory Subjects
[QUOTE]Islamiat / Ethics (Choose any 1 Course) - are both subjects taught by same teacher?

MATHS / G.MATHS (Choose any 1 Course) - both are taught by same teacher - per your previous reply
Ans: It depends on the students Strength and availability of Teachers, like if their is good number of students of each subjects than we will split the class and assign separate teacher and same the case with the teachers also.


Science Group

Per syllabus option = Biology / Computer (Choose any 1 Course) - Has this now changed to Biology OR Computer OR Electric Wiring? (per your previous post)
Ans: Sorry after the meeting with our Course coordinator now it is change to Biology & Computer only. (Plz see the the Syllabus Excel file for further details)

Humanities Group
There is an option - Choose any 2 Course from Following... - there are 22 different subjects - a big choice!


Do some teachers teach several of these subjects?
Ans. Yes, some teachers teach several of these subjects.


Do they teach more than one of the subjects in the same Period?
Ans. Yes, teachers can take more then one subject in the same period. depending upon the availability of teacher or a need for merging the class & subject arise.


Why do most of these subjects not appear in the actual Timetable?
Ans. Plz see the Syllabus "Excel updated file & Timetable" which comprises of all the possible subjects and also optional Subjects.

Class: 9th & 10th (subjects & Optioanl Subjects are almost Same)
Class: 8th - P.G (All subjects are mentioned in timetable class wise)

The Timetable

The timetable you provided has 8 periods for 4 classes. This is the timetable for ONE day - is the timetable the SAME every day of the week?
Ans: Yes timetable remains same every day of the week. But timetable only changes when any teachers left or hired.

I have presented your outstanding Generic Model in meeting it was impressive :) unfortunately i forgot to ask u about this "a" thing in P01-P08 :).

i hope after the code is written it work great according to expectation :).

plz let me know any further Questions, and feel free to ask any thing :)

Best Regards

pipsmultan
05-25-2015, 04:31 AM
Sheets Class Schedules and Teacher Schedules are filled out using a UserForm with code for all the scheduling validations and constraits. They become distributable to teachers, bulletin boards, students et al.
The administrater opens the (Syllabus) Form and select a class (Class 8th). A new form shows showing the Cousres in Class 8th. He or she pick a couse and a new Form loads showing the course, the periods taught, and the teachers assigned to each Period with blnks for unassigned. Whenever the Admin clicks on an empty "Teacher Assigned" box a dropdon apeares with all the teachers qualified and available. the Form can also show any restrictions some teacher may have.
When she is done with the Teacher Assignment Form, he returns to the Class Form to select another course. Or Goes back to the Syllabus form to chose another Class to work on.

I hope this reply finds you great in health and spirits. :)

Your rough draft was pretty neat and good :) but while i was discussing it in the meeting we were not sure how it will look and work in real scenario. :) is it possible when you code your rough draft it transform the timetable in the following format of the Post# 13 (Time Table_Updated.xls)

plz also find the New Updated attachment of "Timetable and syllabus" excel file in Post# 13

High Regards

Yongle
05-27-2015, 12:53 AM
Hi pipsmultan


Away on another project for a couple of days, will post amended code then


thanks

pipsmultan
05-27-2015, 03:45 AM
Hi pipsmultan
Away on another project for a couple of days, will post amended code then
thanks

Thanks and wish you good luck in your Projects. :). looking forward

Best Regards

SamT
05-27-2015, 02:33 PM
is it possible when you code your rough draft it transform the timetable in the following format of the Post# 13 (Time Table_Updated.xls)

When designing a Project of this size it is Best Practice to follow some simple steps in order:


Gather and Record Data
Design Data Display
Determine Rules and Constraints
Lay Out Data Input
Write Code


Steps 1 to 3 are yours and yours alone responsibility. In my opinion as a tutor and previous teacher, Steps 1 and 2 are a good project for those students in the technical classes, (Science, Math, Electrical, et al.) Many of them will be able to use the experience in their future careers.

Gather and Record Data
Organizing data is for the purpose of programming. All such sheets will not be usually be visible in the Master workbook.

You already have all the data gathered. The recording of data must be in simple lists or matrices as in my previous attachment and in the new SchoolTimingMatrix Sheet in this attachment. The TeachersNameList Sheet, as edited, is also an acceptable method, although the coder does not need any formatting except, always make the column headers Bold, Bottom or side Bordered and one size large font than the list font. This is a Best Practice for VBA


One Data zone you have not obviously considered is Student schedules: Matrices by Class of courses and students with Intersections showing Periods. See sheet in attachment. Do not consider saving space by limiting the number of data lists and matrix sheets. It is counter productive. Use as many as desired to organize the data effectively.



Design Data Display
In this attachment, you have two good examples of displaying Data, Teachers Workload and Daily school Timing. Note that Data Displays may not be in the Master Workbook, however blanks templates of each should be included therein.

This is where you let your imagination free. As you have been doing as you recorded data, create a display that has all that you need on it. A worksheet is a good design board, put all of them in one separate workbook. Only place Blank templates in the Master after all design work has been completed and Finalized.

Note that such Template sheets will not usually be visible in the Master workbook. Use as many as you need. When used, the Code will insert them into a new workbook as visible.

Note that some Data is best displayed on a UserForm.



Determine Rules and Constraints
This is where you tell the coder/programmer what must and can't be. These Restraints and Rules should/can be in a Word document with headings for each subject


Course Assignments:
No course can less than 10, nor more then 99 students
No course can less than 1 nor more than 10 teachers
No teacher can teach more than 3 courses in the same period
No student can enroll in 2 courses in the same period
Etc



Teachers Workload
The administration needs a complete sheets in a separate workbook, named ("***") with all teachers, all Classes, all courses for 4 weeks in the future, updated at start of Office each Friday, or the last workday before Friday.

All teachers need a sheet that shows only their workload, for two weeks in the future, updated as above.


The first three steps are usually interactive, that is, you will be moving between each step as thought occur, and errors and omissions become apparent.


Lay Out Data Input
In my mind this means designing the UserForms. However this design process can be done on a worksheet because it is merely an image of what you want it to look like. You can use worksheet controls to represent command buttons. Many professional Programming shops actually use printed images. It may be the most important concept to consider the Form User's workflow while designing the Form. It is best Practice to use a different form for each task. ie assigning a teacher to different courses, inputting a student's courses, et al. The programmer will make suggestions as to true Best Practice as he understand the UserForm possibilites and constraints.

It is best practice to design forms on the smallest computer monitor that will be used to view them, but it is not necessary.

This process is very interactive with step 2 and 3.

I hope this is of help for you in completing the Project in a timely and efficient manner.

ps: Yes the updated timetable is possible, but as you see, you will be doing most of the actual design of what you want and need. st


pps: I did some Best Practice Renaming on the UserForm and the Modules in the attached for your consideration. st

pipsmultan
06-07-2015, 12:22 PM
Hi,

I will be hoping that this reply finds you, and finds you well.

Thank you for your prompt reply and sorry for very late reply as last few days was busy schedule and was not able to keep in-touch with you guys "SamT & Yongle"

but i hope that you all are in good health :) and Yongle got free for their "project" :)

Now i got free from School side bcz Summer vacation is ON. and school will be remain off for 1 month approx :). In that time i will be completing this timetable with all your Help



When designing a Project of this size it is Best Practice to follow some simple steps in order:


Gather and Record Data
Design Data Display
Determine Rules and Constraints
Lay Out Data Input
Write Code



all the 5 steps sound perfectly.


Lay Out Data Input

In my mind this means designing the UserForms. However this design process can be done on a worksheet because it is merely an image of what you want it to look like. You can use worksheet controls to represent command buttons. Many professional Programming shops actually use printed images. It may be the most important concept to consider the Form User's workflow while designing the Form. It is best Practice to use a different form for each task. ie assigning a teacher to different courses, inputting a student's courses, et al. The programmer will make suggestions as to true Best Practice as he understand the UserForm possibilites and constraints.

vey very true :) true best practice is UserForm and i believe that will save a lots of time to while designing the timetable


I hope this is of help for you in completing the Project in a timely and efficient manner.
Yes the updated timetable is possible, but as you see, you will be doing most of the actual design of what you want and need. st
I did some Best Practice Renaming on the UserForm and the Modules in the attached for your consideration. st

thanks that "updated timetable is possible". unfortunately i was not able to see "UserForm and the Modules" as when ever i click on the UserForm the excel program stop working and than it close the whole excel file :(

I will be waiting by hard when the code will be writing and the timetable will be Auto generated by all your kind help :)

plz free to ask anything

Best regards

SamT
06-07-2015, 08:48 PM
Cross posted @ ExcelGuru
generate work schedule (http://www.excelguru.ca/forums/showthread.php?4487-generate-work-schedule)

pipsmultan
06-07-2015, 09:43 PM
Cross posted @ ExcelGuru
generate work schedule (http://www.excelguru.ca/forums/showthread.php?4487-generate-work-schedule)



Apologies for cross-post, forgot to post a *link* to that message of other thread :(

pipsmultan
06-11-2015, 03:04 AM
Dear Respectable member of this forum ", SamT, Yongle: Hope your days are going well :)

please spear some time for my Timetable work, it will be highly appreciating

Best Regards

pipsmultan
06-17-2015, 11:48 PM
Dear Respectable member,

I request please spear some time for my Timetable work, it will be highly appreciating

Best Regards

Aussiebear
06-18-2015, 03:16 PM
There's a good lesson to be learnt here Pipsmultan. Two highly respected contributors to this forum have spent considerable time and effort in trying to assist you in this special project of yours, but you somehow forgot to mention that you had posted the same issue elsewhere. Undisclosed cross posting is very poor etiquette.

While the Internet makes the world a very small place these days, it's even more close knit when you condiser that many members of this forum also enjoy membership of other like minded forums and evidence of cross posting soon become full knowledge.

Whilst I don't speak for SamT and Yongle directly, I do speak on behalf of all contributors to this forum, by suggesting that, as a person asking for assistance you need to show respect to the effort made by others.

pipsmultan
06-18-2015, 10:18 PM
Thank you for your concern "Aussiebear". I really appreciate the advice you have given me. My apologies to the community that i be aware and be conscious next time.

i request the forum to assist me in my timetable work and;

also deeply request "SamT, Yongle" for their assistance as they have spent there valuable time in assisting me and founded better solution for my "auto timetable"

High Regards

SamT
06-19-2015, 05:16 AM
Prepare this (http://www.vbaexpress.com/forum/showthread.php?52615-generate-work-schedule&p=325297&viewfull=1#post325297) first.

pipsmultan
06-24-2015, 09:52 PM
Prepare this (http://www.vbaexpress.com/forum/showthread.php?52615-generate-work-schedule&p=325297&viewfull=1#post325297) first.

Thanks for response i am very grateful. I am working on the Post #17 and submit it asap

Best regards