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 calendarwork 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 separate 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 Show time as 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 separate 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...... :)