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).
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.
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?
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.
*** This bug has been marked as a duplicate of bug 113889 ***
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."
Bah, occurs only in a zh-* locale.
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.
(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.
(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.
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.
Pending review https://gerrit.libreoffice.org/59215 for 6-1
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.
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!!
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.
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.