Easiest way to get either LibreOffice Calc or Excel to import ISO8601 datetimes

Question

Using Python3, I've made a bunch of log files, which include datetimes rendered with the iso_format() method. E.g.

...
2015-04-09T18:31:56.285000  12.7
2015-04-09T18:31:56.285000  12.6
2015-04-09T18:32:16.662000  12.8
2015-04-09T18:32:36.231000  12.4
2015-04-09T18:32:56.235000  12.6
2015-04-09T18:33:56.242000  12.8
...

I want to import these into either Excel (Excel 2011 for Mac, version 14.4.8) or Calc (LibreOffice 4.4.2.2).

I am frustrated that neither of these supports IS8601 right out of the box, especially surprised about Calc given its open source lineage. I have to believe that there are plenty of analysts out there though that need to import data from IS8601 datasets and analyze it in either of these programs.

What are they doing? What is the easiest way to get my ISO8601 fields interpreted quickly in either of these?

http://stackoverflow.com/questions/4896116/parsing-an-iso8601-date-time-including-timezone-in-excel — Tim Williams, Apr 13 '15 at 17:15
Thanks @TimWilliams. With the exception of the last answer there, none of those really passed my threshold of "easy" :) — Travis Griggs, Apr 13 '15 at 18:37

score 1 · Accepted Answer · answered Apr 13 '15 at 18:14

1

Please try:

=LEFT(A1,FIND("T",A1)-1)+MID(A1,FIND("T",A1)+1,LEN(A1))

and format as:

dd/mm/yyyy hh:mm:ss.000

or to suit.

answered Apr 13 '15 at 18:14

pnuts

58,317
11
87
139

That works. Thanks. Is there really no way to do it without having to introduce an additional column? (Also, I'm assuming you meant this for Excel?) – Travis Griggs Apr 13 '15 at 18:46

Easiest way to get either LibreOffice Calc or Excel to import ISO8601 datetimes

1 Answers1