Bug 139934 - FILEOPEN: Date is shown as an integer
Summary: FILEOPEN: Date is shown as an integer
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
4.1 all versions
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: MSO-External-Producers
  Show dependency treegraph
Reported: 2021-01-27 00:05 UTC by Jan Kratochvil
Modified: 2021-02-05 23:18 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

.xlsx (5.84 KB, application/octet-stream)
2021-01-27 00:06 UTC, Jan Kratochvil
.csv correct from GoogleOffice (5.22 KB, text/plain)
2021-01-27 00:06 UTC, Jan Kratochvil
.csv incorrect from LibreOffice (4.92 KB, text/plain)
2021-01-27 00:07 UTC, Jan Kratochvil
Comparison LibreOffice 7.2 master and MSO 2010 (241.58 KB, image/png)
2021-01-28 09:51 UTC, Xisco Faulí

Note You need to log in before you can comment on or make changes to this bug.
Description Jan Kratochvil 2021-01-27 00:05:29 UTC
.xlsx dates 1/19/2021 and 1/20/2021 are shown correctly both by MS Excel and Google Office.
LibreOffice shows them as 44215 and 44216.

Steps to Reproduce:
Open Jan_Kratochvil.xlsx

Actual Results:

Expected Results:

Reproducible: Always

User Profile Reset: No

Additional Info:

Build ID: 00(Build:2)
CPU threads: 8; OS: Linux 5.9; UI render: default; VCL: gtk3
Locale: cs-CZ (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 1 Jan Kratochvil 2021-01-27 00:06:07 UTC
Created attachment 169169 [details]
Comment 2 Jan Kratochvil 2021-01-27 00:06:50 UTC
Created attachment 169170 [details]
.csv correct from GoogleOffice
Comment 3 Jan Kratochvil 2021-01-27 00:07:21 UTC
Created attachment 169171 [details]
.csv incorrect from LibreOffice
Comment 4 BogdanB 2021-01-27 10:47:13 UTC
In your xlsx file the dates are saved as number, and not as dates.

A number is exported to a csv file as a number.
Comment 5 Jan Kratochvil 2021-01-27 10:52:06 UTC
(In reply to BogdanB from comment #4)
> In your xlsx file the dates are saved as number, and not as dates.

So why MS Excel + Google Office show those "numbers" as dates?
Comment 6 BogdanB 2021-01-28 05:46:40 UTC
I tested with import in Google Drive, indeed it transform the number into dates.
Comment 7 Xisco Faulí 2021-01-28 09:51:19 UTC
Created attachment 169220 [details]
Comparison LibreOffice 7.2 master and  MSO 2010
Comment 8 Xisco Faulí 2021-01-28 09:53:00 UTC
Reproduced in

Version: / LibreOffice Community
Build ID: f2389a70da606768a39ee599de6a5b24058734aa
CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Build ID: 3ca42d8d51174010d5e8a32b96e9b4c0b3730a53
Threads 4; Ver: 5.7; Render: default; 


Version (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
Comment 9 Xisco Faulí 2021-01-28 09:53:23 UTC
@Eike, I thought you might be interested in this issue
Comment 10 Eike Rathke 2021-01-28 12:23:21 UTC
The .xlsx attached here is in the old Excel interim XML file format that LibreOffice only partially supports, and number formats given as some default format IDs with cellXfs
<xf numFmtId="..." ...>
apparently are not supported by this filter.

To solve the problem, save the file in Excel once in the standard OOXML .xlsx format and it likely will work also in LibreOffice. Or apply the date format in LibreOffice and save as Excel 2007-365 .xlsx, which should fix things as well.
Comment 11 Jan Kratochvil 2021-01-28 13:01:00 UTC
This format is "Excel export" from https://www.workday.com (which is now being deployed inside Red Hat company). So I do not have an option to choose a different format. Except for using other offices to convert it afterwards.
There are many workarounds but at the end of the day LibreOffice is incompatible while MS Excel + Google Office are compatible.