Excel and timestamps

[John Thompson]

I hope this is more of an Excel question than a J-Field question.

If I export a csv file that includes the field "Time Local", the csv file has a timestamp that looks something like 2016-09-20T16:52:59-06:00. If I open the csv file in Excel, I have to do a bunch of manipulation to get a single column of timestamps that Excel recognizes as timestamps. (Text to columns, separate date and time into separate columns, remove the T's and -6"00's, add the time and date columns, copy and paste as values the resulting column, then delete the time and date columns.)

Is there a simpler way to get Excel to recognize the timestamps as times?

 

[Mikhail Drakin]

What Excel recognizes as timestamp, depends on particular locale settings. Please post here sample text you wish to get. Currently we write ISO8601 (https://en.wikipedia.org/wiki/ISO_8601) format.

 

[John Thompson]

Thanks, Mikhail.

My version of Excel would recognize dates if the T is replaced with a space and the time zone is removed.

MM-DD-YYYY HH:MM:SS

This Excel formula will convert ISO8601 to an Excel date and time value.
=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))

 

[Mikhail Drakin]

Ok, I'll add this format also.

 

[John Thompson]

Thanks!