[andydavidc]

OK, I am Australian. I use s's and u's, and say "zed". I also like my dates in DD/MM/YYYY format, so does my Excel. I plot my BGs in Excel against the timestamp, so I need to convert the MM/DD/YYYY date format to DD/MM/YYYY.

If the below formulas seem complicated, remember you only need to do it once! I have found the easiest way is to make a template spreadsheet with the formulas below into which you can paste your exported Glucose Buddy data.

FYI, if you open the exported CSV file, the date/time data is in column F starting at row 2 (F2).

- Isolate time or date from date/time column.
To isolate time, use formula:

=TIMEVALUE(MID(F2,12,8))

where the "MID" function selects and returns specific characters in the F2 cell - starting at the 12th character (from the left), lasting 8 characters long (this returns only the hh:mm:ss characters in the F2 cell). "TIMEVALUE" tells Excel these characters are time. You may need to change the cell properties (Format > Cells) to "time" instead of "number" to see it properly. Easy!

If you want to isolate the date (keeping it in MM/DD/YYYY format), use the same formula, but change the start position and length to target the date characters in the cell (MID(F2,1,10)), and remove "TIMEVALUE". You don't need to use "DATEVALUE", the US Excel should recognise that it is a date. Remember to change the cell properties to "Date"

Convert MM/DD/YYYY to DD/MM/YYYY
use formula:

=VALUE(MID(F2,4,2)&"/"&MID(F2,1,2)&"/"&MID(F2,7,4))

This formula is ridiculously long because it actually deconstructs and rearranges the characters in the cell, using the character isolation method as above (if you look closely); the "&" sticks it back together. The &"/" manually puts in the separator. This is the only way I know... If your date/time column is in column F, you just need to copy and paste the above formula.

Now that the numbers are in the DD/MM/YYYY format, your European/Australian/non-US Excel should recognise that the numbers are a date.

Convert date to DD/MM/YYYY, but leave in the same cell as the time
I want to graph my sugars against the time and date, so I need the date and time them in the same cell, but in the correct DD/MM/YYYY format. The date is the only issue, so use the deconstruction/rearrangement formula to convert the date, then stick the time back on using the "&". I put in a space (&" ") between the date and time to separate them.

=VALUE(MID(F2,4,2)&"/"&MID(F2,1,2)&"/"&MID(F2,7,4)&" "&MID(F2,12,8))

Excel should read this as a time stamp. If it doesn't look right, you may need to change the cell properties to custom, and use the "type": dd/mm/yyyy hh:mm:ss, or however you want it to be displayed. This will allow you to plot your 5.5 mmol/L against the 29/01/2012 08:35:22.

Hope this helps, keen to know if there are any issues or omissions!