Bug 119013 - Date Format MM/DD/YY not Preserved When reading XLSX or XLS
Summary: Date Format MM/DD/YY not Preserved When reading XLSX or XLS
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.2.0 target:6.1.1
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2018-07-31 07:01 UTC by Kevin Suo
Modified: 2018-08-21 10:58 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
test ods file with date formatted as MM/DD/YY (8.16 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-07-31 07:01 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2018-07-31 07:01:54 UTC
Created attachment 143834 [details]
test ods file with date formatted as MM/DD/YY

The attached ODS file contains cell value formatted as "MM/DD/YY" (09/01/17). When save as xlsx or xls format and reopen, the cell value becomes "YY/MM/DD" (17/09/01).
Comment 1 Kevin Suo 2018-07-31 07:03:53 UTC
This issue was initially reported in the Chinese Libreoffice Community: https://bbs.libreofficechina.org/thread-2206-1-1.html

As the original bug reporter and me can both reproduce, I am marking this as NEW.

The original reporter has stated that it works in versions before 5.4, and is broken in versions after 6.0.5,6.1, 6.0.6, thus a possible regression.
Comment 2 Xisco Faulí 2018-08-01 08:21:30 UTC
The behaviour changed after

https://cgit.freedesktop.org/libreoffice/core/commit/?id=51478cefaa4e265b42e3f67eda0a64767ff3efba

author	Eike Rathke <erack@redhat.com>	2017-04-18 16:57:00 +0200
committer	Eike Rathke <erack@redhat.com>	2017-04-18 17:01:27 +0200
commit	51478cefaa4e265b42e3f67eda0a64767ff3efba (patch)
tree	934887225b1629fba9659e10e1df5e687319db94
parent	e2d3e936ab03274696f235c9d74e247380070f6f (diff)
Resolves: tdf#107012 follow date order of the target locale
... when converting format codes between locales, so en-US MM/DD/YYYY
correctly ends up as de-DE DD.MM.YYYY instead of MM.DD.YYYY

However, I don't know whether it's the expected behaviour or not.

@Eike, could you please comment here?
Comment 3 Volga 2018-08-08 06:11:29 UTC
I think Eike's commit failed to check the locale while doing rearranges, and I think this need to add some exceptions for many other locales that we don't know about.
Comment 4 Eike Rathke 2018-08-15 13:56:34 UTC

*** This bug has been marked as a duplicate of bug 113889 ***
Comment 5 Aron Budea 2018-08-15 14:46:53 UTC
This being closed as a duplicate of bug 113889 contradicts the statement from comment 1:
"The original reporter has stated that it works in versions before 5.4, and is broken in versions after 6.0.5,6.1, 6.0.6, thus a possible regression."
Comment 6 Eike Rathke 2018-08-15 16:10:46 UTC
Bah, occurs only in a zh-* locale.
Comment 7 Eike Rathke 2018-08-15 17:47:59 UTC
So, this happens when *reading* the document, not when writing the document (which bug 113889 was about).

The cause is that the locale for the Excel document is en-US with date order MDY, but then when loading converted to zh-CN with date order YMD so the MM/DD/YY is converted to YY/MM/DD. Which actually would be correct if the original used an en-US locale, but the default-system locale information is lost when saved.

Best probably is to not touch date ordering at all when loading documents.

Btw, the order is preserved if the format is specified as MM\/DD\/YY or MM"/"DD"/"YY because then the literal / slash characters don't represent the locale's date separator.
Comment 8 Aron Budea 2018-08-15 18:43:50 UTC
(In reply to Eike Rathke from comment #7)
> So, this happens when *reading* the document, not when writing the document
> (which bug 113889 was about).
> 
> The cause is that the locale for the Excel document is en-US with date order
> MDY, but then when loading converted to zh-CN with date order YMD so the
> MM/DD/YY is converted to YY/MM/DD. Which actually would be correct if the
> original used an en-US locale, but the default-system locale information is
> lost when saved.
Isn't the locale supposed to be preserved during saving to Excel format? Bug 73063 seems to be about that expectation.
Comment 9 Eike Rathke 2018-08-16 08:14:23 UTC
(In reply to Aron Budea from comment #8)
> Isn't the locale supposed to be preserved during saving to Excel format? Bug
> 73063 seems to be about that expectation.
That's about the locale of one specific format. What I was referring here is that the original default (system) locale in effect when the format was created and stored is not known.
Comment 10 Commit Notification 2018-08-16 16:54:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5b8007afdb97d416ee7c22bf9226e927d61e9bd3

Resolves: tdf#119013 do not over-aggressively reorder date particles

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Eike Rathke 2018-08-16 16:56:27 UTC
Pending review
https://gerrit.libreoffice.org/59215 for 6-1
Comment 12 Commit Notification 2018-08-17 15:18:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=04e12e17cd7a9b7e740299560e8e3e0ba2822ca3&h=libreoffice-6-1

Resolves: tdf#119013 do not over-aggressively reorder date particles

It will be available in 6.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Xisco Faulí 2018-08-20 16:36:18 UTC
Verified in

Version: 6.2.0.0.alpha0+
Build ID: 401cba4c20fbc930f034168872642428d7459218
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: threaded

@Eike, thanks for fixing this!!
Comment 14 Commit Notification 2018-08-20 17:50:18 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0281aa380a20672d55d1d672fd4a43bdcb6c224d

One more "do not reorder date particles", tdf#113889 tdf#119013 follow-up

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2018-08-21 10:58:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=11936b405094cf4a4b1c95b8cbf8e1f2c2bcad05&h=libreoffice-6-1

One more "do not reorder date particles", tdf#113889 tdf#119013 follow-up

It will be available in 6.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.