[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!

[dano]

Thanks for posting that info!

It's always great to have an Excel Guru on board.

[tonebex]

I am extremely intimidated by the reply which explains how to program the GB to other formats of measurement. That is not the fault of those who are trying to assist those seeking assistance, it is just too technical info for many people.

Why couldn't those wonderful people who developed GB just provide a choice in "settings" that would allow us users to select our preferred format (Mine would be the 24 hour clock and dd/mm/year) much as we can do already with our preferred blood glucose measurement format.

BTW, I only discovered GB yesterday, although I had invented it in my mind many times over since I developed type 1 diabetes 40+ years ago. I think it is absolutely brilliant and want to extend my thanks and appreciation to all those involved in the project.

[tsedeski]

the formulas arent returning the time adn date. i get #VALUE! for time adn a number format for the date eventhough the cells are formatted properly.

[mar2a]

I think I just found an easy way to do it!

With Google, I found this page:
http://en.allexperts.com/q/Excel-105...-time-cell.htm

1. Create two new columns.
2. Copy the combined date/time column into each one.
3. Highlight the first copied column. Change the format to Short Date. (Use Format Cells > Date).
4. Highlight the second copy. Change the format to Time (Use Format Cells > Date).

I'm using the American time and date formats, but you can choose whatever format you want.

[mfstacey]

Thank you to all those who have tried to solve this long outstanding issue. Why GB don't just add the preferred date format as an option in settings as they do with weight etc. However I have another problem with this. When I try the various methods to automatically extract the date the trunc() function works well for the first few entries then fails on others. Looking at the data I have the dates in varying formats, a "short" form and a "long" form. When I extracted all my logs from the start of this year I got the first few days as the "short" form and upon which the trunc() functions works fine. The data then changed from the 13th to the "long" form at which point the trunc() function doesn't work. Then back to the "short" form on the 1st Feb and continued with this until the 13thFeb where it changed back to the "long" form. I do see a pattern now of course where it changes on the 1st and the 13th of the month but why????

1/01/13 7:09
01/13/2013 09:14:00
2/01/13 6:01
02/13/2013 05:12:26

[dano]

Have you tried the "Report Generator"? It works very well.

[attention_plz]

I like to work with excel date and time functions. Everything is so beautifully laid. Good tips can be found here http://www.excel-aid.com/excel-date-...unctions.html:)