Possible to have Calc recognize ISO 8601 date format?

Hi. Basically I’m wondering if there is a way to have LibreOffice Calc plot data in the following format correctly:

A               B
20120101T1600Z  1
20120102T1600Z  2
20120105T1600Z  5

Where 20120101T1600Z is the date first of January 2012, 16:00 UTC. My end goal is a scatter plot with dates on the x-axis, correctly spaced according to the time between data points.

1 Like

Just customize your cell date format with the following “Format code”


(assuming your cell Language is set to English)

I can’t get that to work actually. If I enter that format, and enter the time like “2012-01-01 16:00” then the date is displayed like 20120101T1600Z and everything works like it should. However if I actually enter “20120101T1600Z” in the field then the scatter plot turns out VERY wrong.

That is expected :slight_smile: Since you are providing a Custom date format, it is displayed as you request but Calc doesn’t recognize it at input time as a date but as a text string. The solution is to create four columns, for year, month, date and time and then on the fifth use a formula =DATE(A1;B1;C1)+D1

Is there really no other way? You see my input is a csv-file with ISO 8601 dates. I suppose I could use awk or something to reformat the file before opening it with Calc, but that’s not very elegant. Is there really no way to add a “true” date format in Calc?

Only if the ISO 8601 formats are added. You can request that as an enhancement at bugzilla.
Meanwhile paste this on column C =DATE(LEFT(A2;4);MID(A2;5;2);MID(A2;7;2))+TIME(MID(A2;10;2);MID(A2;12;2):wink: and this on column D =B2 and create your plot from columns C and D :wink:

That works, thanks a lot! :slight_smile:

How about if you reformat things a bit so that column A looks like this:


2012/01/01 16:00

2012/01/02 16:00

2012/01/05 16:00

Then, in Calc, in Format, Cells, Date, User Defined, you could have “YYYY/MM/DD HH:MM”.