We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Possible to have Calc recognize ISO 8601 date format? [closed]

asked 2012-04-03 11:00:05 +0200

Quantumboredom gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-15 23:06:54.283179

2 Answers

Sort by » oldest newest most voted

answered 2012-04-06 22:36:36 +0200

Pedro gravatar image

Just customize your cell date format with the following "Format code"


(assuming your cell Language is set to English)

edit flag offensive delete link more


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.

Quantumboredom gravatar imageQuantumboredom ( 2012-04-07 03:28:21 +0200 )edit

That is expected :) 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

Pedro gravatar imagePedro ( 2012-04-07 07:43:10 +0200 )edit

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?

Quantumboredom gravatar imageQuantumboredom ( 2012-04-07 12:40:22 +0200 )edit

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);) and this on column D =B2 and create your plot from columns C and D ;)

Pedro gravatar imagePedro ( 2012-04-07 14:30:50 +0200 )edit

That works, thanks a lot! :-)

Quantumboredom gravatar imageQuantumboredom ( 2012-04-07 15:05:23 +0200 )edit

answered 2012-04-06 18:37:59 +0200

chimak111 gravatar image

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".

edit flag offensive delete link more

Question Tools


Asked: 2012-04-03 11:00:05 +0200

Seen: 7,533 times

Last updated: Apr 06 '12