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)
Version:
(earliest affected)
4.1 all versions
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
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:


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

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
Description:
.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:
44215

Expected Results:
1/19/2021


Reproducible: Always


User Profile Reset: No



Additional Info:
libreoffice-calc-7.0.4.2-3.fc33.x86_64

Version: 7.0.4.2
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]
.xlsx
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: 7.2.0.0.alpha0+ / 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

Version: 5.2.0.0.alpha0+
Build ID: 3ca42d8d51174010d5e8a32b96e9b4c0b3730a53
Threads 4; Ver: 5.7; Render: default; 

and

Version 4.1.0.0.alpha0+ (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.