Ask Your Question

Incorrect date display in calc for xls files [closed]

asked 2014-09-17 13:34:58 +0200

revolver gravatar image

updated 2014-09-18 11:12:06 +0200

Whenever I open my financial reports in libre calc the date fields are all displayed as: 31/12/1899 00:00:00. Microsoft Office displays all the dates in the file correctly.

image description

I uploaded the file (with the irrelevant info omitted): link text

EDIT: Apparently, libre calc resets all the dates to null values when it reads the file. I took another xls file and made sure the dates were all valid then used MS Office to save it as an xlsx. Openning the xls file with LO calc resets the date values. To my surprise, openning the xlsx files worked, the dates didn't reset. Here's the xls file for your viewing pleasure (watch how the dates are valid before openning the file). It's a bug in calc's xls support, I should just add it to LO's bug tracker, Right?

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 2016-03-03 19:03:38.546618

1 Answer

Sort by » oldest newest most voted

answered 2014-09-17 14:25:10 +0200

erAck gravatar image

1899-12-31 is the NULL date, seeing that means the cell does not contain a serial date value or a 0 value formatted as date. I can't see the file you linked, browsing there just tells me "browser does not allow data to be written" and I'm not willing to change any browser settings for that domain..

edit flag offensive delete link more


I know it's the null value, It shouldn't be the null value. Openning it with Microsoft Office shows all the dates just fine. As for the file, Here it is on another file sharing file: here

revolver gravatar imagerevolver ( 2014-09-17 14:48:26 +0200 )edit

Whatever you did, that file is an Excel-XML file and contains literal 1899-12-31T00:00:00.000 for each cell: <cell><data ss:type="DateTime">1899-12-31T00:00:00.000</data></cell> If that is the data file you loaded then Calc just displays what's there.

erAck gravatar imageerAck ( 2014-09-17 16:34:12 +0200 )edit

Have to agree with @erAck. When I open your file in Excel Starter 2010 it complains "not the file structure like the extension" and there are no dates displayed only numbers "0".

horst gravatar imagehorst ( 2014-09-18 22:13:12 +0200 )edit

Question Tools

1 follower


Asked: 2014-09-17 13:34:58 +0200

Seen: 762 times

Last updated: Sep 18 '14