Adding Your Timetable to Your Calendar in Outlook 2013

Published Saturday, 22 November 2014

I stopped procrastinating for once and went through my list of things to do, and one of those was to put my school timetable into my calendar.

Sounds simple, right?

Not quite so, we're talking a total of six periods a day, each week (30 periods) for a two week timetable (60 periods). Some of those are single periods, and some doubles. Oh, and some after school stuff as well.

It's actually quite complicated when you look at it. Each period needs to be added, and contains:

  • a Subject
  • a Location
  • a Start Date/Time
  • an End Date/Time
  • a Description (Dirty class name and Teacher's name, e.g. 10X/AB1 - Mrs Ann Example)
  • a Reminder set for the start of the period

There's a lot to it, and in the past I'd to go through each period inputting each of these variables myself. Fancy User Interfaces are great n' all, but when you need to do mass data input, they can be somewhat... limiting. At least until now

Spreadsheets are great for mass data input because of the speed you can input large amounts of data, however you need to know what goes where without a handy label telling you. UIs are great because they show you what goes where, but try to enter more than a dozen entries and it's really, really boring. Like really, really, really boring. Like listening to politicians giving us false promises sort of boring.

The trick to this is that many calendar clients allow you to import csv (comma separated values) files which contain event details and import them into your calendar.

Step 1 - Making the events

Start out with a new excel document and insert this header row:1

Subject | Description | Location | Date | Time | EndDate | EndTime | Reminder | ReminderDate | ReminderTime

From there, start by entering all of the subjects. If you have an event which has a break in the middle, i.e. I have a double period of Chemistry, with a period of lunch in the middle, then enter the subject twice - this can be seen on lines 5, 6, 9, 10, 14 and 15 of the image below

Various subjects inserted under the subject heading

Next, add their descriptions and locations. In this case, I want to keep my classes, teachers and classrooms anonymous (to protect me from fellow students and my teachers from you), so they're blurred :)

Class names and Classrooms added

After that, insert the start dates, start times, end dates and end times. I do one day, copy and paste it into the appropriate start dates and end dates, increment it and do it for the next and so on. After that, I go back and do the start times, then go back again and do the end times. It's not as complex as it sounds!

Start Dates/Times

The last step for this document is to add the reminder. In this case, I want a reminder to go off at the end of a lesson to tell me where I want to go next, so that makes the reminder really simple to set up. For each one, we need to set Reminder to 1, the date to the date it happens, and the time to the time it starts. This can be done by setting every cell in the reminder column to '1' and copy/paste the start dates and start times into the reminder dates and reminder times:

So, first set the entire column to 1. Do this by setting the first two cells to '1', selecting those two cells, then double clicking the little square in the bottom right of the selection (we do the first two cells to stop it doing a number increment, i.e. 1,2,3,4,etc.)

Set the first two cells to '1'After double clicking the square, the entire column will be set to '1'

Afterwards, select the all of the start dates and times and copy them using Ctrl+C

Columns selected and copied

Then paste them into the reminder by selecting the top left reminderDate cell and hitting Ctrl+V

Select the top-right cell of the reminderDate cellPaste the dates and times in

Once that's done, your file should look something like this

The subjects, descriptions, locations, start times, end times and reminders all set up

Save that file as a comma delimited CSV (and as an xlsx if you have multiple worksheets)

N.B. If you have various holidays you want to work around, copy/paste the work you have already done and do a find/replace on the two weeks for the first two weeks of that 'non-holiday block'. You will then end up with a bunch of events with dates just after the last holiday

Step 2 - Importing the events

Make sure you have closed Excel before continuing, or Outlook will complain it can't open your csv

Open up outlook, click 'File', Click 'Open & Export' and click 'Import/Export'. You should see this:

Outlook Import Window

Click 'Import from another program or file', click next, then click 'Comma Seperated Values' and click next again

Select the csv you made in the last step and click open. Select 'Allow duplicates to be created' and click Next

Outlook Import Window and duplicate option selected

Select the calendar you want to import to. In this case I'm using my 'Timetable' calendar on my school email

Choose the calendar to import to

You will then be presented with a window asking you to check the actions to be performed. Check the Checkbox next to 'Import "yourcalendar.csv" into folder: yourcalendar'. When you click the checkbox, a window will open allowing you to map the fields. If a window doesn't open, click the 'Map Custom Fields' button on the right. This is the window you'll be presented with:

Field Assignment Screen

Next, click and drag the items from the left box to their appropriate partners on the right. If you used my template, it should look like this, albeit your box won't be so tall (this image took so long to construct in paint!)

Field Assignment Screen, with options set

Once you're happy with the assignments, click OK, then click Finish. Your calendar will then be imported into outlook and you can look at the events and be happy

The Calendar

Notice that they only appear once, and don't repeat bi-weekly? Neither Outlook imported calendars or CSV calendars support recurring events, so we have to do that bit ourselves, but it's easy, really.

First, find the date you want the calendar to repeat to, like the next holiday. In my case for the june calendar, that half term ends on Wednesday, 22nd July 2014. Open notepad, word, or even the start menu and type in the date you finish that set on, then select it with Ctrl+A and hit Ctrl+C to copy it

Start Menu with 22/07/2015 typed into the start menu

Afterwards, go to through each event in your calendar (I use work week view, because it spaces them not too close, but not too far) and set up the recurrence using the recurrence button. You can't select them all and do the recurrence at one! I wish you could, but Outlook doesn't support that.

Calendar with Recurrence button highlighted

When you open the recurrence menu, you'll see this:

Recurrence Menu with Default Settings

Set it to recur every two weeks, and paste the end date into the 'End By' Box. Once you get into the swing, use shortcut keys to get around further, specifically: Alt+C, 2, Alt+B, Tab, Tab, Ctrl+V, Return.

Recurrence Menu with custom settings

Once you're done, you should be able to scroll through your calendar and see all of the events, along with distinct gaps for the holidays

Calendar with events

  1. You can download a header template in XLSX format