Description: My system is Ubuntu and the locale is set to en-US. I have a document in that a range of cells is formatted as date with Italian locale (DD/MM/YYYY). Whatever date I type in the fields the date and month always gets reordered. If I type 03/05/2019 Calc changes it to 05/03/2019. User input should never be corrected, unless invalid. Steps to Reproduce: 1. Set locale to en-US 2. Format a cell with a locale that has DD/MM/YYYY dates 3. Type in a valid date. Actual Results: The day and month in the date are inverted, whatever they are. Expected Results: Day and month not inverted. Reproducible: Always User Profile Reset: No Additional Info: Version: 6.2.2.2 Build ID: 1:6.2.2-0ubuntu2 CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: kde5; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: CL
Your input is not corrected. It is taken from you (according to your locale, which is set to en-US, i.e., using MM/DD/YYYY date ordering); the date is converted from the text to the date internal representation; and then it's shown according to the cell's format, which puts parts in a different order. This is correct behavior.
I see your point of view. However this is very user-unfriendly and really no software does that. Analogy: if a form accepts a price in euro everyone is expected to type euros in there. This is valid also for users that live in USA and have dollars as "system" currency. The same applies to dates. If a document contains a column of dates formatted as (DD/MM/YYY), thinking and typing them all in as MM/DD/YYYY is very hard. I'd say it's almost impossible to do without errors. Another thing I see now is that it's not consistent. Typing in a date like 21/12/2001 will NOT swap day and month. Very hard to use.
(In reply to SStefanek from comment #2) > and really no software does that Wrong. MS Excel (the major spreadsheet software) does that; that's the industry standard. > Analogy: if a form accepts a price in euro everyone is expected to type > euros in there. This is valid also for users that live in USA and have > dollars as "system" currency. Your analogy is wrong. Spreadsheet is not a form itself. *Format string* is never used to interpret the input (other than when it tells that the cell is text, so no interpretation at all is needed), because format string may be however complex, with different parts, and even invalid itself. Formatting is related to display, not to input. > Another thing I see now is that it's not consistent. Typing in a date like > 21/12/2001 will NOT swap day and month. Very hard to use. It *is* consistent. When you type it like that, it's treated as invalid date, and then simply not converted to date at all, staying string (and then you can't use it in calculations like finding date difference etc).
(In reply to SStefanek from comment #2) > However this is very user-unfriendly > ... > The same applies to dates. If a document contains a column of dates > formatted as (DD/MM/YYY), thinking and typing them all in as MM/DD/YYYY is > very hard. I'd say it's almost impossible to do without errors. In opposite, the current behavior is much more user-friendly than the proposed change. If you are from the US, you likely think of dates using the national standard (month-day). And having to always re-think how to put it to a cell accepting a date would be a burden. So proper selection of your locale allows you to disregard any formatting; and e.g. use string like "jan/3" if you wish. Additionally, you only consider a case of formatting where only day and month exchange positions. But there are more possible formats. You may use ISO format (2019-04-30); or you may use home-grown format like `"day "DD" in year "YY" month "MM`... and treating format as input mask would impose huge difficulties to user.
> Wrong. MS Excel (the major spreadsheet software) does that > that's the industry standard. Nope. I have just tried and Excel does not override my input. It may not interpret it as date unless typed in the way it expects but that's a different story. Previous versions of Calc didn't do that either: it started swapping dates with a recent update. If I have typed AA/BB/CCCC and the input matches both the cell format and the system format then I probably wanted it to be AA/BB/CCCC and not BB/AA/CCCC. > In opposite, the current behavior is much more user-friendly than the proposed change. I guess you are right. I'll find a way to deal with that. Thank you for your time.
Created attachment 151179 [details] Screencast of Excel 2016 swapping date parts on input (In reply to SStefanek from comment #5) > Nope. I have just tried and Excel does not override my input. It may not > interpret it as date unless typed in the way it expects but that's a > different story. Previous versions of Calc didn't do that either: it started > swapping dates with a recent update. Here you can see this in action. I'm Russian, and use Russian locale (D/M/Y); so I did just the opposite: I set the cells to en-US, and then entered the date, so you may see that the date as I entered (1.3.2019) converted to 3.1.2019 on entry. So I see that Excel indeed does that.
Thank you for taking time to make a video. It helps nailing down the issue. So on windows with Excel it's a matter of system date settings. I have English locale and date format set to YYYY-MM-DD. If I type in any format that is not like YYYY-MM-DD (say it's DD/MM/YYYY) it is interpreted like text and preserved: not perfect but still OK. If I type in YYYY-MM-DD then it works like in your video: it changes it to whatever the format is set on the cell, even to the insane MM/DD/YYYY. OK. So Excel really interprets input related to system locale. Calc on windows has a different behavior. With global English locale and system date format YYYY-MM-DD it still interprets XX/YY/ZZZZ input as MM/DD/YYYY regardless of what format is set on the cell (unless MM is greater than 12). So I guess that the system setting is honored only partially: global locale is used but specific date format is ignored. This seems to be case also on linux: with LC_TIME set to it_IT and everything else set to en_US the dates are swapped on input. I'm pretty sure this used to work before. My dates weren't swapped when typing. It started when I have upgraded from Ubuntu 18.* to Ubuntu 19.04. I have also tested Calligra Sheets (which does not preserve input) and Gnumeric (which preserves it).
(In reply to SStefanek from comment #7) > So on windows with Excel it's a matter of system date settings. True. > Calc on windows has a different behavior. With global English locale and > system date format YYYY-MM-DD it still interprets XX/YY/ZZZZ input as > MM/DD/YYYY regardless of what format is set on the cell (unless MM is > greater than 12). So I guess that the system setting is honored only > partially: global locale is used but specific date format is ignored. LibreOffice does not take system locale into consideration "completely". Well - sorts of: LibreOffice uses system locale to detect the *default* locale - if user doesn't set LibreOffice locale to some explicit value in Options->Language Settings->Languages. And then, it doesn't take into account any OS-specific additional settings, like intl.cpl settings other than language on Windows, or LC_TIME on Linux. There's an enhancement request on that; but it has never worked differently with LO/OOo. LO had introduced accepted date formats to allow users to fine-tune what is accepted as date.
I'm trying to play with "Date Acceptance Patterns" but I can't figure out how they work. Whatever i put in the setting the input behavior doesn't change: it still interprets all the date patterns as dates and still swaps day and month. By the way, I still have LibreOffice 5.4.5.1 on my laptop and that one works perfectly (same system and LO settings). First of all it seems to interpret the input based on the cell format. If it's set to an English locale then 01/02/2019 will lead to 02 Jan 2019. If it's set to Italian locale then 01/02/2019 will lead to 01 Feb 2019. Moreover it never swaps day and month: what the user types is what will end up in the cell. I think that the previous behavior was correct. Obviously it might be a matter of personal preference, "industry standard" or whatever. But since currently there doesn't seem to be a way to fix it, I suggest that the behavior should be configurable: either prefer cell format for date interpretation or prefer global locale.
(In reply to Szymon Tomasz Stefanek from comment #9) > By the way, I still have LibreOffice 5.4.5.1 on my laptop and that one works > perfectly (same system and LO settings). First of all it seems to interpret > the input based on the cell format. If it's set to an English locale then > 01/02/2019 will lead to 02 Jan 2019. If it's set to Italian locale then > 01/02/2019 will lead to 01 Feb 2019. Moreover it never swaps day and month: > what the user types is what will end up in the cell. > > I think that the previous behavior was correct. Obviously it might be a > matter of personal preference, "industry standard" or whatever. But since > currently there doesn't seem to be a way to fix it, I suggest that the > behavior should be configurable: either prefer cell format for date > interpretation or prefer global locale. That was the fix for tdf#116579: https://git.libreoffice.org/core/+/dfb9138b8b5a239b46f189a717999bcaff19aa79. Eike: since this indeed worked differently before 6.1, could you please clarify if it's a bug or intended change?
One of the comments in tdf#116579 made me notice another nasty side effect. - I enter 01/02/2019 into an empty cell. - The day and month gets swapped and we get 02/01/2019. Let's say it's OK. - Then I realize that 2019 is wrong - I wanted it to be 2018 - I enter edit mode by double clicking, change the year only and press enter - The day and month gets swapped again and we have 01/02/2018 now To allow editing cell without the users going crazy the current cell format must be taken into account as first. Only if it does not lead to a recognizable date then other formats can be tried.
This "1. Set locale to en-US" and this "2. Format a cell with a locale that has DD/MM/YYYY dates" explains it. The cell input is parsed according to the system locale (or the locale set under Tools->Options) in its M/D/Y order and then displayed using the date order of the applied date format. That is expected. At least for so far empty cells there is no indication what input would be accepted so using the system locale is logical. The problem here is editing an existing date in such constellation, because after F2 the date is presented in the edit format of the applied format in D/M/Y order but again parsed as M/D/Y of the system locale. To be investigated.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/478e051f4ea13b15120fdf74faf94a6c2cfcb50c%5E%21 Resolves: tdf#125109 prefer edit format's acceptance patterns and YMD order It will be available in 6.3.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/72848 for 6-2
*** Bug 125652 has been marked as a duplicate of this bug. ***
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-2": https://git.libreoffice.org/core/+/4d51531130988d87839bb7b0c077a5df43b2a6be%5E%21 Resolves: tdf#125109 prefer edit format's acceptance patterns and YMD order It will be available in 6.2.5. 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.