Free School Schedule Template
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the formula takes care of the rest. See the animated image above.
The time ranges are entered in an Excel defined Table that expands automatically when new values are added, no need for dynamic named ranges.
The template has hours divided into 10-minute intervals, follow the instructions below and you will with ease create another interval if you prefer to use that.
If you don't like the look of the ranges I am using you can easily change the conditional formatting color that highlights the ranges in the schedule.
There is a workbook for you to get at the very end of this article.
How I created this dynamic template
The schedule contains formulas, conditional formatting, and an Excel defined Table, it updates instantly when you add/delete new records.
Weekdays
- Select cell G1
- Type Monday
- Select cell H1
- Type Tuesday
Repeat above steps for remaining cells and weekdays in cell range H1:M1.
10-minute intervals
- Select cell F2
- Type 8:00
- Select cell F3
- Type 8:10
- Select cell range F2:F3
If you want to use a 12-hour clock AM/PM then simply select all time values in column F and press CTRL + 1 to open a dialog box that allows you to change cell formatting.
Press with left mouse button on "Time" and then pick a format that you prefer.
Create headers
- Select cell A1
- Type Weekday
- Select cell B1
- Type Subject
- Select cell C1
- Type Start
- Select cell D1
- Type End
Create Excel defined Table
- Select cell A1.
- Press CTRL + T to create an Excel defined Table.
My table looks like this:
It contains some random data.
Enter array formulas
- Select cell G2
- Press with left mouse button on in the formula bar
- Type:
=IFERROR(IF(SUMPRODUCT((Table1[Start]=$F2)*(G$1=Table1[Weekday]))=0, "", INDEX(Table1[Subject], SUMPRODUCT((Table1[Weekday]=G$1)*(Table1[Start]=$F2)*(MATCH(ROW(Table1[Weekday]), ROW(Table1[Weekday])))))), "")
- Press and hold Ctrl + Shift
- Press Enter
Copy array formula
- Select cell G2
- Copy cell (Ctrl +c)
- Select cell range H2:M2
- Paste (Ctrl + v)
- Select cell range G2:M2
- Copy (Ctrl + c)
- Select cell range C3:M50
- Paste (Ctrl + v)
Create conditional formatting
- Select cell range G2:M50
- Go to tab "Home"
- Press with left mouse button on "Conditional Formatting" button
- Press with left mouse button on "New Rule..."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Type =SUMPRODUCT((G$1=Weekday)*($F2=>Start))
in "Format values where this formula is TRUE" field.
- Press with left mouse button on "Format..." button
- Press with left mouse button on Border tab
- Press with left mouse button on to create three borders
- Press with left mouse button on OK
- Press with left mouse button on OK
Repeat above steps with remaining conditional formatting rules:
The border and fill formatting are also shown in the picture above.
1 : =SUMPRODUCT((G$1=Weekday)*($F2>=Start)*($F2<End))
2:Â =SUMPRODUCT((G$1=Weekday)*($F2=End))
3:Â =SUMPRODUCT((G$1=Weekday)*($F2=Start))
4:Â =SUMPRODUCT((G$1=Weekday)*($F2>=Start)*($F2<End))
Schedule category
In this post I am going to add one more function to the weekly schedule I built in a previous […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]
Templates category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
Excel categories
9 Responses to “Free School Schedule Template”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
when i add an entry to Friday 12:30, it does not work?
TCKY,
I opened the attached excel file and added an entry to Friday 12:30 - 14:00 and it works here.
Your excel version?
[...] Free School Schedule Template [...]
Hey Oscar,
Thanks so much for your post on the weekly schedule.
I am currently planning my school schedule and I'm having a hard time managing time conflicts/overlaps.
For example,
I have Course 1 @ 8-9 am on Monday
and Course 2 @ 8-9 am on Monday as well.
What I think might work is:
1) When a duplication occurs on the left chart (day/subject/start/end), highlight both duplication time schedules
2) If time is in 10 min intervals and a course is about 1 hr, three's enough space to both course names in the conflicting time slot in the weekly calendar
3)Another idea for 2 is that a text dialogue can be added to the sheet permanently so that if you press with left mouse button on a conflicting time slot on the weekly schedule, the names of the courses that are in conflict can be depicted there.
Anyways thanks for your time. If you have any advice on the coding or how I should go about in this project or if my ideas would even work, I would greatly appreciate your feedback.
Joseph
So I just reviewed the main formula and I realized that to see conflicts, the course start and end has to be the same.
Another issue that occurs is:
Course 1 is lets say 9:00-11:50
Course 2 is at 9:30-12:50
^That type of overlap cant be detected.
Also, the highlight formula doesn't really help play a role in this overlap situation...
Looks like some form of excel VBS might be needed.
I appreciate the time and effort for for the help. Meanwhile, Imma try to find a solution. If my solution works, I'll post it here :D
Joseph
I have changed the time interval from 10 min to 15 min. Now the formatting for the following time are not working. i.e. top/bottom border and subject
15:30
16:15
17:00
17:45
18:30
19:15
19:30
20:00
example:
Monday Martial-Arts 17:00 18:00
I am missing the subject and the top border, if I change it to
Monday Martial-Arts 17:15 17:45
The bottom border is missing
So error times are the same for Start and End
I found your free-school-schedule-template and find it useful but not quite what I need. I am not a newbie, nor as experienced in formulas as you are, and would love to find a solution to make my life easier.
SITUATION:
Event Staff Scheduling. When we attend events we need to schedule staff to cover the booth while others may be in meetings or attending events.
BOB =
Monday from 1-4pm and 6-8pm
KEVIN =
Tuesday from 9-12, 1-2, 3-6
Wednesday from 9-12, 2-4
SARA =
Monday from 9-1, 3-6
Tuesday from 1-3, 4-5
Wednesday from 10-1, 3-5
I tried reconfiguring your example so that the TIME BLOCKS run horizontally (top)
Names would be in rows (left side)(in reality have 25 people)
I think a Sheet for each DAY would work well
I think it would be great to have a data entry (the table you have for classes) on a separate sheet... easier to import/input the data.
Why do the first 3 conditional formatting rules apply to a different selection of cells rather than the whole table? My table is larger so the cell range needs to adjust but the top 3 appear to be applying formatting to an EMPTY section just to the right of the live table data.
Just wondering how it is used to know if I need to replicate that when I have more rows and columns. Thanks
Hi
Thank you so much for this. This has been what I have been wanting for ages. I've messaged twice over the last four days as I worked crazily on developing a dynamic timetable that imports and manipulates data from our Master Schedule and adapts it to the format needed by this timetable.
My university files are all within the Google Suite, so once I mastered the Excel version, using yours as a foundation, I went over to Google. Two days later, I succeeded in importing data dynamically and updating the timetable for each teacher in real-time.
I thought I'd share the link to the basic equivelent of a static timetable that I created in Google Sheets. It's not perfect (I have to learn about the conditional formatting of borders in Google Drive), but it might help someone else who finds this page like I did.
It also has a conflict checker built in to make sure that no two classes overlap.
Here is the link to the sheet https://docs.google.com/spreadsheets/d/16xmtGbBIrD3Irm66zIs-qJbn0dKXriiyCZV9RSGfYBI/edit?usp=sharing
Thank you again. I wouldn't have been able to achieve any of this without this webpage.