Description: When using DD/MM/YYYY date format, typing 31/8/22 automatically formats to 31/08/2022, but typing 1/8/22 automatically formats to 08/01/2022. Aug. 1st becomes Jan. 8th. Steps to Reproduce: 1. On menu: File/New/Spreadsheet 2. Left-click on "A" to select the entire column A. 3. Right-click on "A" to open the pop-up menu, and select "Format Cells". 4. Select Category "Date", Language "Portuguese (Brazil)", and Format "31/12/1999". Click "Ok". This will apply the DD/MM/YYYY date format. 5. Type 31/8/22 in cell A1. This will automatically format to 31/08/2022. 6. Now type 1/8/22 in cell A2. This will automatically format to 08/01/2022, instead of 01/08/2022. Actual Results: Typing 31/8/22 automatically formats to 31/08/2022, but typing 1/8/22 automatically formats to 08/01/2022. Aug. 1st becomes Jan. 8th. Expected Results: Since the date format is set to DD/MM/YYYY, 1/8/2022 means Aug. 1st and should automatically format to 01/08/2022 (Aug. 1st), not to 08/01/2022 (Jan. 8th). Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Version: 7.2.7.2 Build ID: 20(Build:2) CPU threads: 8; OS: Linux 5.18; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded OpenGL vendor string: Intel OpenGL renderer string: Mesa Intel(R) HD Graphics 4000 (IVB GT2) OpenGL core profile version string: 4.2 (Core Profile) Mesa 21.3.9 OpenGL core profile shading language version string: 4.20 OpenGL core profile context flags: (none) OpenGL core profile profile mask: core profile OpenGL core profile extensions: OpenGL version string: 3.1 Mesa 21.3.9 OpenGL shading language version string: 1.40 OpenGL context flags: (none) OpenGL extensions: OpenGL ES profile version string: OpenGL ES 3.0 Mesa 21.3.9 OpenGL ES profile shading language version string: OpenGL ES GLSL ES 3.00 OpenGL ES profile extensions:
How it is interpreted depends on the language used on default style and non-other has been applied for the cell. Please attach a minimal sample file.
Created attachment 181645 [details] sample file, as requested.
[Automated Action] NeedInfo-To-Unconfirmed
Not reproducible in: Version: 7.5.0.0.alpha0+ / LibreOffice Community Build ID: a9d225df2f8772e21435523ca20df1ece37390e4 CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: pt-BR (en_NG); UI: en-US Calc: threaded This usually depends on the date acceptance pattern in the locale. You can try to switch the Locale setting or adjust the Date acceptance patterns in the language configuration from Tools > Options > Language Settings > Languages
(In reply to Ezinne from comment #4) > This usually depends on the date acceptance pattern in the locale. > > You can try to switch the Locale setting or adjust the Date acceptance > patterns in the language configuration from Tools > Options > Language > Settings > Languages Thank you. Switching the Locale setting to "Portuguese (Brazil)" and adjusting the date acceptance patterns to "D/M/Y;D/M" fixed it. However, I still find very confusing that 31/8/22 is interpreted as Aug 31st, but 1/8/22, is interpreted as Jan 8th. This inconsistency really looks like a bug to me.
(In reply to Joao Carvalho from comment #5) > (In reply to Ezinne from comment #4) > > This usually depends on the date acceptance pattern in the locale. > > > > You can try to switch the Locale setting or adjust the Date acceptance > > patterns in the language configuration from Tools > Options > Language > > Settings > Languages > > Thank you. Switching the Locale setting to "Portuguese (Brazil)" and > adjusting the date acceptance patterns to "D/M/Y;D/M" fixed it. > > However, I still find very confusing that 31/8/22 is interpreted as Aug > 31st, but 1/8/22, is interpreted as Jan 8th. This inconsistency really looks > like a bug to me. Hi Eike, Could you please comment on this ?
I don't reproduce with sample file and Portuguese locale in LO. Please read help on Date acceptance patterns that Ezzine pointed to. What you didn't mention is what was the previous setting that let to bug?
If it was like M/D, then this is NotABug.
Let me first define three different Language settings that I tried: Language settings 1: User interface: Default - English (USA) Locale setting: Default - English (USA) Date acceptance patterns: M/D/Y;M/D Language settings 2: User interface: Default - English (USA) Locale setting: Default - English (USA) Date acceptance patterns: D/M/Y;D/M Language settings 3: User interface: Default - English (USA) Locale setting: Portuguese (Brazil) Date acceptance patterns: D/M/Y;D/M I am not 100% sure, but I think that I was using Language settings 2 when I first started this thread on Aug 6th. What I know for sure is this. If I change the Language settings to one of the the above, and then try the "Steps to Reproduce" that I initially wrote, I get the following results. Language settings 1 and 2: Typing 31/8/22 automatically formats to 31/08/2022, but typing 1/8/22 automatically formats to 08/01/2022. Aug. 1st becomes Jan. 8th. Language settings 3: Typing 31/8/22 automatically formats to 31/08/2022, and typing 1/8/22 automatically formats to 01/08/2022, as I expected. Please note that my individual problem is solved. All I have to do is use Language settings 3. What I think is really confusing and should be looked at is the fact that Language settings 1 and 2 produce conflicting results. If 1/8/22 was to be interpreted as Jan 8th, then 31/8/22 should not be recognized as a valid date. On the other hand, if 31/8/22 should be recognized as a valid date, then 1/8/22 should be interpreted as Aug 1st.
(In reply to Joao Carvalho from comment #9) > ........... > I get the following results.,,,,, > > Language settings 1 and 2: Typing 31/8/22 automatically formats to > 31/08/2022, but typing 1/8/22 automatically formats to 08/01/2022. Aug. 1st > becomes Jan. 8th. > > Language settings 3: Typing 31/8/22 automatically formats to 31/08/2022, and > typing 1/8/22 automatically formats to 01/08/2022, as I expected. What do you see formatting those results as ISO format yyyy-mm-dd
(In reply to m.a.riosv from comment #10) > What do you see formatting those results as ISO format yyyy-mm-dd Ok, so this is what I tried and this is what I found. Again, let me first define three different Language settings that I tried: Language settings 1: User interface: Default - English (USA) Locale setting: Default - English (USA) Date acceptance patterns: M/D/Y;M/D Language settings 2: User interface: Default - English (USA) Locale setting: Default - English (USA) Date acceptance patterns: D/M/Y;D/M Language settings 3: User interface: Default - English (USA) Locale setting: Portuguese (Brazil) Date acceptance patterns: D/M/Y;D/M Steps: 1. On menu: File/New/Spreadsheet 2. On menu: Tools/Options/Language Settings/Language 3. Set one of the three "Language Settings" I defined above. 4. Left-click on "A" to select the entire column A. 5. Right-click on "A" to open the pop-up menu, and select "Format Cells". 6. Select Category "Date", Language "Portuguese (Brazil)", and Format "1999-12-31". Click "Ok". This will apply the YYYY-MM-DD format Code (ISO 8601). 7. Type 31/8/22 in cell A1. 8. Type 1/8/22 in cell A2. Results: - Language settings 1 and 2: Typing 31/8/22 automatically formats to 2022-08-31, but typing 1/8/22 automatically formats to 2022-01-08. Aug. 1st becomes Jan. 8th. Language settings 3: Typing 31/8/22 automatically formats to 2022-08-31, and typing 1/8/22 automatically formats to 2022-08-01, as I expected.
Please take a look in the help. But seems to me that the patterns must be in accordance with the selected local. Please, @Eike what do you think?
(In reply to m.a.riosv from comment #12) > But seems to me that the patterns must be in accordance with the selected > local. Again, what I think is really confusing and should be looked at is the fact that both Language settings 1 and 2 seem to produce inconsistent results when we compare the manner in which 31/8/22 and 1/8/22 are interpreted and formatted. If 1/8/22 was to be interpreted as Jan 8th (which means that the first number should be handled as month), then 31/8/22 should not be recognized as a valid date (31 is not a valid month!). On the other hand, if 31/8/22 should indeed be recognized as a valid date (which means that the first number is day, and the second number is month), then 1/8/22 should be interpreted as Aug 1st. The current behaviour is inconsistent and extremely confusing. When either language settings 1 or 2 (see above) are used, it seems as if the first number may be interpreted either as month (if between 1 and 12) or as day (if between 13 and 31). The way I see it, the manner in which date is interpreted should depend only on the current locale setting and/or date acceptance pattern setting. Once these are set, interpretation approach should not behave diferently for different inputs: the first number should always be interpreted as day, or it should always be interpreted as month, whatever the input is. This inconsistecny in how the date is interpreted is really confusing and frustrating. Also note that Language settings 1 or 2 (see above) produce the exact same results, eventhough the date acceptance pattern is different: 1 is M/D, 2 is D/M. I expected Language settings 2 to produce the same results as Language settings 3, because both use date acceptance pattern set to D/M. While 3 behaves consistently as I expected it to behave, 2 behaves exactly like 1. But 1 uses date acceptance pattern set to M/D, while 2 and 3 are both set to D/M. Doesn't something seem off here?
One problem seems to be that a locale's implicit full date acceptance pattern internally is always considered first, so if for en-US the patterns were set to D/M/Y;D/M then while parsing input it becomes M/D/Y;D/M/Y;D/M so 31/8/22 is accepted only with D/M/Y but 1/8/22 already with M/D/Y. That probably should be fixed and the implicit pattern not be evaluated if the specified Date Acceptance Patterns don't say so. I'd guess that first was done to prevent users to shoot themselves in the foot as patterns completely not matching the locale's patterns somehow don't make sense, but you can't have both. Apart from that, the following occurs: First, date input is tried to be matched against the Date Acceptance Patterns (including the locale's implicit full pattern). Second, if that does not match a valid date and the input cell is formatted to a date already then the input is tried to be matched against the format's locale's default date acceptance patterns. So here for the explicit pt-BR locale the default patterns are D/M/Y;D/M . For case #1, that is first try M/D/Y;M/D then for 1/8/22 the current locale's M/D/Y and for 31/8/22 of the format's D/M/Y is successful. For case #2, that is try M/D/Y;D/M/Y;D/M (with the implicit full pattern) that is successful for both, again with M/D/Y and D/M/Y. For case #3, that is try D/M/Y;D/M that is successful for both with D/M/Y. So even if evaluating the implicit pattern would be removed, case #1 with the then resulting first M/D/Y;M/D then D/M/Y;D/M may still result in unexpected but correct behaviour.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b759f6720b50487200740a6202e21774897b44b2 Resolves: tdf#150288 Do not prepend the locale's full date acceptance pattern It will be available in 7.5.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/c/core/+/138545 for 7-4
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/df6601c2432fa6353c9641cedea918fc524ae31d Resolves: tdf#150288 Do not prepend the locale's full date acceptance pattern It will be available in 7.4.1. 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/51f6ec80e7b02689c19dd3917043d1088c2e7843 tdf#150288: sc: Add UItest It will be available in 7.5.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.