Diary Excel Export

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 'non-standard' date format format makes it very tricky. Well, I was bored for a few minutes so decided to write the to-datetime 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 top-most 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 bottom-right 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.

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

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

Changing the format seems straightforward but there are a number of pitfalls:

1. Although you can stick a date-time 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 date-time is ultimately text - No matter which way you change it you will always need to do a DATEVALUE or TIMEVALUE conversion to get real date-time 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 Date-Time. 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 x-axis and overlay data from multiple days on the same graph).