ISO 8601 Title

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:


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


Calc (German):



Local Date and Time

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



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.

ISO 8601-parsing in Excel (German version)

ISO 8601-parsing in Excel (German version)

Sample Files

Related Links



Leave a Reply

Your email address will not be published. Required fields are marked *