Class Timetable Template

About this template

When to use it

One teaching session per row for a weekly timetable — period, subject, day, time, instructor, room, year group and hours. Three sheets — Dashboard, Input data, Usage guide — and only Input data is typed into. Replace the template subtitle in B3 with your department and semester.

What the dashboard holds

Key metrics in B7:E7 — rows entered, Hours total, Hours average, Hours max — read column H of Input data. Summary by day in B12:F16 groups the same column by the day in column C, one row each for Mon to Fri. The Weekly hours by year group table (B20:C23) is typed in by hand.

Filling order

1. Rows 2–21 of Input data take Period, Subject, Day, Session length, Instructor, Room, Year group, Hours (A–H). Ten sample sessions ship, from Programming Fundamentals on Monday to Industry Project on Friday.
2. Write the day as the short form Mon, Tue, Wed, Thu, Fri (note 5). Those five strings are what the summary counts.
3. A subject that meets twice a week takes two rows (note 6), and each row carries only that session’s hours. Data Structures and Algorithms in the sample are separate sessions of the same course.
4. Column H is formatted to one decimal, so a 50-minute period entered as 0.8 or a triple period as 3 both work.
5. Replace the four year-group totals (Year 1–4: 16 · 18 · 20 · 12) with your own.

How the formulas calculate

• =COUNT(‘Input data’!$H$2:$H$21) counts sessions with an hours figure.
• The day block is =COUNTIF(‘Input data’!$C$2:$C$21,$B12), =SUMIF(‘Input data’!$C$2:$C$21,$B12,’Input data’!$H$2:$H$21) and the matching AVERAGEIF, with a share of =IFERROR(C12/COUNT(‘Input data’!$H$2:$H$21),0).
• Under the year-group table, =SUM(C20:C23) and =AVERAGE(C20:C23) — 66 and 16.5 as shipped.
• No formula checks for a clash. Note 7 asks you to sort the Day, Session length and Room columns together and read down; if you would rather have it flagged, =IF(COUNTIFS($C$2:$C$21,C2,$D$2:$D$21,D2,$F$2:$F$21,F2)>1,”Clash”,””) in a spare column does the same for rooms, and swapping $F for $E does it for instructors.
• The ranges stop at row 21, which is enough for twenty sessions. Copy the last row down and widen $C$21 and $H$21 for a fuller week.

Know this first

The year-group table at the bottom is not the sum of column G. Its shipped figures add to 66 hours while the timetable above holds 22, because the table is meant to carry a whole department’s load and the input sheet only carries the sessions you have typed. Either replace those four numbers by hand each time, or point them at the sheet with =SUMIF(‘Input data’!$G$2:$G$21,$B20,’Input data’!$H$2:$H$21) so they follow the timetable. Note also that column D is headed Session length but holds a start-to-end time such as 09:00~10:50; the number the dashboard adds up is column H.

Examples

• Row 7 — period 4 · Capstone Design · Wed · 14:00~16:50 · Jung Ha-neul · Lab A · Year 4 · 3 hours.
• The ten shipped sessions total 22.0 hours, so Hours average reads 2.2 and Hours max reads 3.0.
• Wednesday and Friday carry 5 hours each because both hold a three-hour studio session; Monday, Tuesday and Thursday carry 4.
• Every day holds two of the ten sessions, so all five share cells show 20.0%.

Common mistakes

• Writing days as “Monday” or “M”. The summary counts “Mon” and nothing else.
• Putting a whole week’s hours on one row for a subject that meets twice.
• Typing “2h” or “110 min” into column H, which turns it into text.
• Assuming the year-group table updates itself when you add a session.