Recent Posts in DAFNE Online Mobile

Aug 22, 2011

Tom Holt 2 posts

Topic: DAFNE Online Mobile / 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.

Aug 22, 2011

novorapidboi26 1,819 posts

Topic: DAFNE Online Mobile / Diary Excel Export

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

Aug 23, 2011

Simon 578 posts

Topic: DAFNE Online Mobile / Diary Excel Export

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

Aug 23, 2011

Tom Holt 2 posts

Topic: DAFNE Online Mobile / Diary Excel Export

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).

Aug 22, 2011 Tom Holt 2 posts	Topic: DAFNE Online Mobile / 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.

Aug 22, 2011 novorapidboi26 1,819 posts	Topic: DAFNE Online Mobile / Diary Excel Export Great walk though, and well done for working it out............

Aug 23, 2011 Simon 578 posts	Topic: DAFNE Online Mobile / Diary Excel Export What date format do you need? I'm sure I could convert it in the diary export - just didn't know what excel wanted

Aug 23, 2011 Tom Holt 2 posts	Topic: DAFNE Online Mobile / Diary Excel Export 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).