Tom Holt
DAFNE Graduate
King's College Hospital, Camberwell
2 posts

Hi All,
If you want to produce your own graphs from the excel (ok, CSV) export then you have a tough task ahead of you. The 'nonstandard' date format format makes it very tricky. Well, I was bored for a few minutes so decided to write the todatetime conversion. To use it, do the following:
1. Export diary as CSV. 2. Open in Excel. 3. Create an extra column (mine is right after the TIME column). 4. Paste this formula into the topmost data cell (should be row 2).
=(DATEVALUE(CONCATENATE(MID(B2,(FIND(" ",B2)+1),((FIND(",",B2)(FIND(" ",B2)+1))))," ",(LEFT(B2,(FIND(" ",B2)))),(MID(B2,(FIND(",",B2)+2),4)))))+(TIMEVALUE(RIGHT(B2,5)))
Nice huh?
5. Point yer mouse to the bottomright corner of that cell to find the little black drag square (not sure what the proper name is) and drag the formula all the way down the page. 6. You should now see some crazy numbers there (e.g. 40770.29167), that is just how excel stores its dates in the background. To make some sense of these numbers you can convert to dates or times by formatting the cells appropriately. In any case, the values should work as either dates or times as you wish. 7. Oh, one more thing; Make sure you save your document as a excel workbook because retaining the csv format is likely to lose your formula.
Enjoy, Tom.

novorapidboi26
DAFNE Graduate
NHS Lanarkshire
1,800 posts

Great walk though, and well done for working it out............

Simon
Site Administrator
Sheffield Teaching Hospitals
558 posts

What date format do you need? I'm sure I could convert it in the diary export  just didn't know what excel wanted

Tom Holt
DAFNE Graduate
King's College Hospital, Camberwell
2 posts

Changing the format seems straightforward but there are a number of pitfalls:
1. Although you can stick a datetime value in a single column in excel, it will only allow you to present it as either date *or* time, not both. 2. Currently, all the columns in excel are formated as 'general' as a result of the conversion from csv to excel  It is impossible to specify a column format as being date or time using the csv protocol. 3. Because it is csv, the datetime is ultimately text  No matter which way you change it you will always need to do a DATEVALUE or TIMEVALUE conversion to get real datetime values into your worksheet. Changing the date format may just make this conversion a bit easier than what I have given above.
I will have a think about it but ultimately I think you may need 3 columns! Date, Time and DateTime. This may actually be a benefit for graphing the results as it would make things more flexible (e.g. you could have time of day in the xaxis and overlay data from multiple days on the same graph).
