ISO 8601 Parsing in Excel and Calc

If you have comma-separated data which has timestamps in ISO 8601-format (e.g. 2018-11-20T14:26:12+01:00) collected e.g. by a datalogger as SerialLogger and you want to use that in Microsoft® Excel or LibreOffice Calc both spreadsheet apps do not recognize ISO 8601-format as date/time but as text. To parse ISO 8601 to date and (UTC) time you do not need Visual Basic or LibreOffice Basic. A formula is sufficient.

UTC Date and Time

The following formula will parse the ISO 8601 date and time string 2018-11-20T14:26:12+01:00 and take the timezone into consideration. You get the date and time in UTC (Universal Time Coordinated). If the ISO 8601 is located in cell A2:

Excel and Calc:

=IF(MID(A2,20,1)="+",TIMEVALUE(MID(A2,21,5))+DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8)),-TIMEVALUE(MID(A2,21,5))+DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8)))

IMPORTANT: If you use non-English Excel and Calc the upper formula does not work. You have to fit the commands to your language. E.g. for German:

Excel (German):

=WENN(TEIL(A2;20;1)="+";ZEITWERT(TEIL(A2;21;5))+DATWERT(LINKS(A2;10))+ZEITWERT(TEIL(A2;12;8));-ZEITWERT(TEIL(A2;21;5))+DATWERT(LINKS(A2;10))+ZEITWERT(TEIL(A2;12;8)))

Calc (German):

=WENN(TEIL(A2;20;1)="+";ZEITWERT(TEIL(A2;21;5))+DATUMWERT(LINKS(A2;10))+ZEITWERT(TEIL(A2;12;8));-ZEITWERT(TEIL(A2;21;5))+DATUMWERT(LINKS(A2;10))+ZEITWERT(TEIL(A2;12;8)))

 

Local Date and Time

To ignore the timezone to get just the local date and time the formula is:

=DATEVALUE(LEFT(A2;10))+TIMEVALUE(MID(A2;12;8))

Datevalue

Witn the formula above you get a datevalue when the cell in question is formated as a number. For the upper example you get 43424,6431944444 which you can format e.g. to MM/DD/YYYY hh:mm or just to time or whatever you prefer.

Sample Files

• ISO 8601 in Microsoft Excel (XLSX)
• ISO 8601 in LibreOffice Calc (ODS)

Related Links

• My Stack Overflow posting
• KDSmart-Dokumentation – Excel formula for Converting ISO-8601 Timestamp to Excel Date