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

Terry

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

Quote:

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

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.

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

Type in cell C1 =(Replace(B1;20;6;

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.

"

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

Thanks

Henk

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.

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.

Quote:

Originally Posted by hgvandel ... 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. |

Similar Topics:

© laptopgpsworld.com About