Description: One of the recent LO releases (I suspect 6.2.5 or 6.2.4) introduced the following misbehavior when entering a date into a Calc cell. I have never observed this bug in earlier releases. Steps to Reproduce: 1. Format a cell as - Category = Date - Format = 1999-12-31 (Format code "JJJJ-MM-TT", ISO 8601) - Language = German (Germany) 2. Click the empty cell and enter a date using German style "<Day>.<Month>.<2-digit year>", e. g. "11.7.19". The pattern is correctly recognized and displayed as "2019-07-11", as it has always been in previous versions of LO. 3. Now that the cell is no longer empty, click it again and overwrite the existing date with the exact same input as above, "11.7.19". Actual Results: The same input is now incorrectly interpreted as "<Year>.<Month>.<Day>" and displayed as "2011-07-19" (instead of the correct "2019-07-11"). Expected Results: The input should have been correctly interpreted as "2019-07-11" again. Date acceptance patterns shouldn't depend on the previous content of a cell. Reproducible: Always User Profile Reset: Yes Additional Info: Version: 6.2.5.2 (x64) Build ID: 1ec314fa52f458adc18c4f025c545a4e8b22c159 CPU threads: 8; OS: Windows 6.3; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: threaded
Confirmed as described with: Version: 6.3.0.1 Build ID: 41ac97386aba908b6db860cfb4cfe2da871886ae CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: en-AU (en_AU.UTF-8); UI-Language: en-US Calc: threaded I also tried a similar thing with language as "English (Australia)" instead, and the date entered in the cell as 11/7/19: the same thing happens. First time, it is interpreted as expected. Second time, day and year are reversed. So it seems it is not language-specific!
Life would be easier if the date was input like displayed in this case.. On one hand users want be able to do that, on the other hand complain if they don't. It works on an empty cell because there is no visible indication whatsoever for the user which date particle order is expected and then the LibreOffice locale's order is preferred. For already existing date content the display format's date particle order is preferred. And yes, this is somewhat locale specific because in these cases the input locale's DMY or MDY order doesn't match the display format's YMD order, which leads to this effect. Taking a stab at this.
I propose to handle it this way: 1.) In neither case shall the current cell content have any impact on how a date input is interpreted. This should only be influenced by (a) the cell format and (b) the language/locale settings. 2.) If a date is input according to the cell format (= using the same separator as the cell format), then it should be interpreted accordingly, regardless of language/locale settings. Example: If a cell is ISO formatted (YYYY-MM-DD), then any input using "-" as separator should be interpreted as "Y-M-D". 3.) If a date is input using different separators than the cell format, the language/locale settings should be applied. Example: In German language, any input using "." as separator should be interpreted as "D.M.Y". I deal a lot with ISO formatted date cells in German language and have always been able to enter the dates either as "Y-M-D" or as "D.M.Y" with both being correctly accepted by Calc.
(In reply to Lenge from comment #3) > 1.) In neither case shall the current cell content have any impact on how a > date input is interpreted. This should only be influenced by (a) the cell > format and (b) the language/locale settings. As said, with an empty or text content there is no indicator for the user what date input could be accepted. So preferring the current LibreOffice locale is logical, and is what always was done. Now when there is already date content, the date is expected to be edited in the edit format of the display format's locale (MM/DD/YYYY for en-US, DD/MM/YYYY for en-GB, DD.MM.YYYY for de-DE, ...) > 2.) If a date is input according to the cell format (= using the same > separator as the cell format), then it should be interpreted accordingly, > regardless of language/locale settings. > Example: If a cell is ISO formatted (YYYY-MM-DD), then any input using "-" > as separator should be interpreted as "Y-M-D". Only the separator is not a sufficient criterion. Consider en-US MM/DD/YY vs en-GB DD/MM/YY, for the ISO case also an underlying current locale with D-M-Y pattern. They have both to be considered and you're lucky if the year was a 4-digit input and the day >=13. > 3.) If a date is input using different separators than the cell format, the > language/locale settings should be applied. > Example: In German language, any input using "." as separator should be > interpreted as "D.M.Y". Which actually depends also on the active date acceptance patterns, not (just) the locale. The default date acceptance patterns depend on the locale, but can be changed. Again, things get complicated if, for example, the cell is formatted for a German locale with D.M.Y pattern, but the default LibreOffice locale has Y.M.D pattern. > I deal a lot with ISO formatted date cells in German language and have > always been able to enter the dates either as "Y-M-D" or as "D.M.Y" with > both being correctly accepted by Calc. Earlier there were only two possibilities: date input either according to the current locale and date acceptance patterns, or in ISO format. The cell format was never considered. "Easy" (except when a locale pattern is D-M-Y or M-D-Y ..). Then users wanted to be able to edit a differently formatted date in the locale it is displayed. They also wanted to be able to enter a date over an existing one in the same format. And at the same time understandably everyone wants to be able to enter a date according to the current locale settings.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/7de96e9f7b69354fd5b15e1276678000bc386568%5E%21 Resolves: tdf#126342 obtain date particle order from pattern match It will be available in 6.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/75490 for 6-3 https://gerrit.libreoffice.org/75491 for 6-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-3": https://git.libreoffice.org/core/+/71b7e419b705b421443981e222ab5968cab4decb%5E%21 Resolves: tdf#126342 obtain date particle order from pattern match It will be available in 6.3.0.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://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-2": https://git.libreoffice.org/core/+/7af89de46ff4a40a8161fd629584d54cfdeadee1%5E%21 Resolves: tdf#126342 obtain date particle order from pattern match It will be available in 6.2.6. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/841ee616a5e5d191365c82359943a0171956e03a tdf#126342: sc_ucalc: Add unittest It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.