Sorting out dates

Posted By Paul Cooper |  last update 19-May 2010

I need to try and get reliable data from our theatre system and who is doing what. The data in the Consultant resonsible for the list' field is unreliable ( dont ask......) in the theatre system. I'm told the most reliable source is the surgeons rota.  This is available on the intranet as an excel spreadsheet.

i thought automating data extracation would be easy. Courtesy of IT ive got a spare computer on my desk running linux ( was gentoo, now ubuntu)

so i can retrieve the file with wget and then parse out the data i need.

The aim is to put it all into a perl hash of hashes - so extracting other data in the future will be straightforward, and i could even do it as a web page.

The hash would be

{date}=>( {on call}=>surgeon1, {theatre} +>surgeon2 )

so to get the person on call for  4 April :

value for {20090404}{on call} = person on call.

to get theatre surgeon for days of a week , just put it in a loop

for date =startdate to enddate ( eg 4 April 2009 -10 April 2009

substitue each value of 'date' into the formula (20090404,20090405,20090406 etc etc )

then surgeon =name stored in {date}{theatre}

which will give the name on a particular date and rota

(i appreciate this wont actually run as code!)

its possible to expand as well and i could pull data from a number of rotas on the same principle.

this is the spreadsheet

First problem :

date field isnt formatted as a date - its text.

the formatting isnt consistent  - some dates have dots and some dont.

So i thought a regular expression would extract the bits I want.I played around with this sort of stuff.

/ (\d{2}) \. (\d{2}) \. (\d{4}) /

2 numbers followed by a dot, then 2 numbers then a dot then 4 numbers

/ (\d{2}) \.* (\d{2}) \.* (\d{4}) /

2 numbers followed by 0 or 1 characters followed by 2 numbers followed by zero or 1 characters followed by  4 numbers.

but it didnt sort out all the dates consistently.

 bit more fiddling around should get it fixed though with the help fo the Date::Manip module in perl

But some  thoughts :

• Whoever produced  the rota doesnt know about date formatting in Excel
• Whoever produced the rota must have spent a good while typing in each date - thats what must have happened because there are different typos in many date fields.
• This must be the level of Excel /IT expertise , or ignorance that many users have.
• And whats the best way to do something about it, if we want to to. Does ECDL or similar cover this , and how far into the course?

i know  for purists excel isnt the correct software to do rotas, but for small departments it gets used because its relatively straightforward and available

• introduce a rota package an spend time and money training people on it
• going and having a quiet word ("we've noticed you are doing the rota, do you know about ......)
• Introduce a 'rota template' that all shall use ?Produce an 'advice for rotamakers using Excel ' handout
• Just let things be.?