Timetable Challenge: Part 3

In earlier posts, I wrote about the challenges our school was facing as we continue to grow in size and deal with the issue of timetables and our response to this by moving towards a 7 day timetable.

One of the issues I encounted was around the use of calendars (specifically Microsoft Outlook and the Ultranet) to schedule events and create my work program:

 I am struggling at the moment to find an efficient and effective way of managing this 7 day cycle. I use Microsoft Outlook to manage my calendar\work program and set each of my classes to repeat weekly. Under this new 7 day cycle, I am unable to create a re-occuring appointment that occurs 7 days apart, as weekends are included in the cycle. Given it takes 3 weeks for the cycle to return back to Day 1 being on a Monday, I had thought I could create a reoccuring appointment to reoccur every 3 weeks, however with days taken our for public holidays and other events, this confuses the cycle and makes it difficult. Other than entering each class I teach individually for the term, I’m struggling to find a way to do this.

I am pleased to report that I have found a solution to this problem (thanks to a Google Search and these instructions) by using Microsoft Excel to create a list of my events and then import these into Outlook. Who knew you could import events from Excel into Outlook!

I’ll attempt to explain the process:

I started by creating a spreadsheet in Excel that used the Outlook fields as the headings for each of my columns.

I created a sepearte worksheet for each of the times I teach (9am, 10am, 11:30am, 12:30pm, 2:15pm)

I entered all of the classes that I taught at that time over the 7 day cycle (eg: Day 1 at 9am, Day 2 at 9am, Day 3 at 9am etc..) with the class being in the subject column and the room being in location.

Not wanting to enter each date individually, a google search found a formula that produced what I needed.

=IF(WEEKDAY(C2)=6,C2+3,C2+1)

In an attempt to explain what the above means, the formula looks at the day in C2 (in this case Monday). Using the WEEKDAY function, Excel can count each of the days of the week (1=Sunday, 2=Monday, 3=Tuesday etc…). When it arrives at a Friday (the 6th Day using the WEEKDAY formula) it is set to add 3 days to the next date that appears. For all other days,  it simply adds 1 day; thus removing weekends from my list of days.

Once I had entered the first 7 classes of the timetable cycle, I used the fill down function to obtain the rest of my classes for the term. To set the event in my calendar to appear as busy, I entered “2” in the Showtimeas column.

I repeated this process for the classes I had at the other times throughout the day.

I then collated all these classes onto 1 worksheet of a seperate spreadsheet.

The next step was to select which area of the spreadsheet I wanted imported into Outlook. This involved highlighting all of the cells that contained data (including the column headings) and naming this range. The easiest way to do this was to enter it into the box next to the left of the formula bar.

From here, I saved the file (I had to save it using the 97-2003 version of Excel to import it), closed Microsoft Excel and opened Microsoft Outlook

From the File menu, I selected Open and Import.

I selected Import from another program or file and selected Microsoft Excel

Using Browse I selected my file and made sure I set the options to Allow duplicates to be created.

I selected my calendar as the destination

It then informed me that my DATA range would be imported.

This has now meant that all of my classes now appear on the correct days of my calendar !

It has also saved me the pain and trouble of entering each class individually. As I have my outlook calendar linked to my iPad and my iPhone, this has also been very handy as I move from room to room this year for my lessons.

Now… If I could just work out a way to import events into the calendars in Ultranet Spaces…… 🙂

4 thoughts on “Timetable Challenge: Part 3

  1. Lamos10

    Scott, what patience and determination you must have to work this out! All I can say is WOW! Thanks so much for sharing. When I read posts like this, it makes me realise how little I know, but that doesn’t matter, because there is always someone out there who is ready to share. Well done and thanks!

    Reply
    1. mrduncan Post author

      Hi Linda – Thanks for the comment. I’m sure there’s some quote about frustration being the mother of invention… and in this case… I was getting rather frustrated with how I was going to manage all this.

      In this information age it’s amazing how easy it is to find a solution or answer to life’s questions. I’d like to hope that the desire to seek out new information, ideas and solutions as well as a willingness to share the experience and this new knowledge is an attribute we can instil in the students that we teach.

      Reply
  2. Lindsay

    Hi Scott, is there any way you could post an example of this spreadsheet so I can understand better how to lay it out? That would be great. Thanks, Lindsay

    Reply
    1. Scott Post author

      Hi Lindasy,

      After a bit of a hunt around on some old backup drives, I managed to find the original excel files I used for this.

      This file sets up the days so that they appear at the top of my calendar in Outlook.

      This file lists all my of the classes and the times\days I used to teach them.

      Hope it’s some help to you.

      I’ve since moved over to Google Calendar but haven’t had the need to set up a cycle calendar again since I have now changed schools.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *