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.
Just customize your cell date format with the following “Format code”
YYYYMMDDTHHMMZ
(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 
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)
and this on column D =B2 and create your plot from columns C and D
That works, thanks a lot!
How about if you reformat things a bit so that column A looks like this:
A
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”.