Changing time stamp format in AutoRoute 2010

Hi,

I downloaded Autoroute 2010 today and did a little playing with it.
Transfered a GPX file into Excel, todo a little determining a trip we did.
The timestamp in the Excelfile is a not familiar to me, see below.

2009-11-27T20:20:30+-1:00.

On this side of the ocean we are used to this timestamp
27-11-2009 20:20:20.

Is there anybody out there who can explain how to convert the strange timestamp into our timestamp

Henk

Because the imported file is CSV, I don't think you could convince Excel to convert the yyyy-mm-dd to dd-mm-yyyy. Excel would see the input as numbers, rather than a date so formatting the cells would probably give you some stange number. An Excel macro to break up and reassemble the numbers would be possible though.

Terry

Henk, I am having trouble reproducing your situation. If I understand your problem correctly, it is not so much the format of the TIME, but rather the DATE. I note that when I open a GPX file in Excel, it gives me options for formatting the date information and if I select DMY it will format the data the way you prefer.

Spadeflush,

Thank you for your reply.

2009-11-27T20:20:30+-1:00 this is in textformat.

During a search on the internet I found out that this is the date and time format in ISO 8601.
Microsoft will implement this in the new version of Excel 2010.
I couldn't not find how to change this format to the usual date and time format.

Henk

I managed to convert the ISO 8601 timestamp used in Autoroute 2010 to a numeric value.

I did the following, used Excel 2002
In field A1 is the timestamp 2009-11-27T20:20:20+-1:00 as text .

I used the Replace function in Excel.

To remove the ‘T’ from the timestamp I did the following.

Type in cel B1 =Replace(A1;11;1; “ “) expl. (the eleventh character, one character)

To remove the “+-1:00” and make this cel mumeric.

Type in cel C1 =(Replace(B1;20;6)*1 expl. (the twentieth character, 6 characters)

The +-1:00 is the time zone you are in. Thus GMT -1.00 is the Netherlands.

If there is a better solution, let me know.

Henk.


Netherlands

I don't understand this step.

I might have used the "Mid", "Left", "Datevalue", and "Timevalue" functions to extract the text I want such as (assuming the data in cell A1)

B1=datevalue(left(a1,10)) with the format set to dd-mmm-yyyy for the date and

C1=timevalue(mid(a1,12,8)) with the format set to hh:mm:sec for the time.

Okay.

I made a small mistake, I forgot a bracket in the "replace sentence".

Type in cell C1 =(Replace(B1;20;6;))*1


Explanation . (after the twentieth character, remove 6 characters) and the multiply by one is to change text to numeric.

Multiplying by one, is placing a 1 in an empty cell, use copy and Paste Special.

"In the center section of the Paste Special box where it says Operation, put a tick mark in the radio button beside Multiply and click OK. "

I am not that good in Excel, but your solution will works also I suppose.

Thanks

Henk

OK, Henk, thanks for clearing that up for me. I understand now. Your solution is really quite clever. I never used "Replace" before nor was I aware that you could make it numeric by multiplying by one, so I learned something.

I would add one little refinement that 'saves' a column and is a little more compact, by 'stacking' your 'Replaces' as follows.

B1=(REPLACE(REPLACE(A1;11;1;" ");20;6,)*1

with the B1 cell formatted for a date/time format of your preference.

This is 'better' than what I submitted above, IMHO.

Spadeflush thank you, for remembering me the stacking methode.
This methode is realy quite nice.

I am using the excel spreadsheet by importing a GPX file for determination the speed we drove.


Using this formula I found on the internet.

=6371.1*((2*BOOGSIN(WORTEL((SIN((RADIALEN(A6)-RADIALEN(A7))/2)^2)+COS(RADIALEN(A6))*COS(RADIALEN(A7))*(SIN((RADIALEN(B6)-RADIALEN(B7))/2)^2)))))


Henk.