This is probably only useful to you if you use Google Calendar (or possibly other appllications that accept .csv imports - ?iCal, perhaps?), and your workplace issues rotas in their own inimitable way - e.g. as an xls file.
This was prompted by a move to work in Acute General Medicine in Oxford.
No problem should ever have to be solved twice. Therefore, this is my solution.
I wanted my rota info to show up as a calendar in Google Calendar. Unfortunately, work only provides rotas via a browser, or in an idiosyncratic .xls, using whatever date and time formats that the author personally favours (I assume).
(As an aside - XKCD has covered this topic - date conventions and ISO 8601 - with the usual authoritative tone)...
I'm indebted to Kevin Burke who came up with much of this solution here.
So:
- You can import calendars to GCal, as long as they are in .csv format.
- Oxford Medicine rotas come as an odd .xls, one that seems to be in "Western European (Apple Macintosh/Icelandic)" format.
- By tweaking column headers and exporting as .csv, you can create a file which GCal accepts!
Disclaimer - I've used LibreOffice throughout - those using Excel may find a few differences.
Put simply, your .csv just needs a strict set of column headings for GCal to accept it.
These are as follows:
Subject, Start Date, Start Time, End Date, End Time, All Day Event, Reminder On/Off, Reminder Date, Reminder Time, Meeting Organizer, Description, Location, Private
Every event (every row in Excel) needs to have a Subject, Start Date, and Start Time. The other headers are optional and you can mix and match them as much as you please.
Because I'm feeling kind, I've supplied a template .csv, which you should edit to suit your needs e.g. in LibreOffice or Excel. You can get it here, or:
https://docs.google.com/file/d/0B5OjGQrd1AYyWklnVTRKSGNWWHc/edit?usp=sharing
I recommend copying your .xls columns into the right places. Counter-intuitively, you should put your shift times in under "Location" so you can see them in GCal, on your phone etc.
The actual column "Start Time" is irrelevant as you will be putting "TRUE" in the "All Day Event" column.
Also - Make sure you FORMAT your columns appropriately (by selecting each column, going to "format"... "cells"... or similar). The formats you need are MM/DD/YYYY for dates (note this is neither ISO 8601, or UK, but US standard), and HH:MM AM/PM for times. GCal is very picky about this.
Weirdly, in my case, the values in the cells still show as DD/MM/YYYY, but as long as you've set the formatting to MM/DD/YYYY, things will work.
( Check your dates once you've put this into GCal! You don't want a mix-up between 3rd April, and 4th March, for example! )
Then - "Save As" a .csv (ignoring the error you'll receive saying some formatting will be lost).
Upload to one of your Google Calendars.
I HIGHLY RECOMMEND you create a NEW calendar and call it something descriptive e.g. "AGM Rota". Do NOT import the .csv to your DEFAULT calendar. By using a separate calendar you can delete the whole thing if you've made an error (e.g. DD/MM/YYYY instead of MM/DD/YYYY) and try again. If you upload to your main calendar, you'll have to go through and delete each event manually.
Make sure you tick to display this calendar.
If things have gone well, you should have your rota displayed as all-day events, each with shift times as "locations", along the top of your calendar! Mine has the letter codes, e.g. DT for Day Take, D for Day shift, O for Off etc, but experiment to find what works for you.
You can even share this with your loved ones / job-share partner / pet hamster / whatever, to make sure everyone knows how hard you're working.
Have fun.
