"5-1-15" (entered today 2015-05-30 into a Calc cell) will result in "05.01.15" being meant to mean 2015-01-05 in a German locale. It will end up with "05/01/15" meaning 2015-05-01 im a US-English locale. "5-13-14" will not be recognised as a date in both these locales. It is assigned to the cell as a text. This may supposedly be triggered by the fact that 13 isn't a valid number for a month. The examples are from a question posted in http://ask.libreoffice.org/en/question/51340/why-does-date-format-only-work-on-dates-less-than-13/ and tested by me in V4.4.3 Calc under both mentioned locales. The questioner was much irritated because he expects the date value 2014-05-13 for the second input. The only locale independent date format named in ODF 1.2 is the complete YYYY-MM-DD format from ISO 8601 (with 4 digit year). Allowing for sloppy abbreviations may meet the expectations of some users. It's a mess nevertheless. If any abbreviation in dash delimited dates shall be accepted it should, however, be restricted to variants of Y-M-D formats without exception. The present behaviour, as described, is error-prone including risks much exceeding the very small possible use of trying to adapt the "recognition" to locales.
I understand your concerns, and can see this being a problem. However, unfortunately the suggested behavior would cause a lot of annoyance and confusion for users in different countries where a different format is used instead of Y-M-D. Not only because they aren't used to it, but also because a lot of different programs use locale-dependent date conversions. However, what if it was more apparent for the user how their input was converted, or will be converted to a date? Is there a way to show this on the UI? Dear UX team, can you consider ideas for making date input less error-prone?
(In reply to Wolfgang Jäger from comment #0) > ... Allowing for sloppy > abbreviations may meet the expectations of some users. It's a mess > nevertheless. If any abbreviation in dash delimited dates shall be accepted > it should, however, be restricted to variants of Y-M-D formats without > exception. > > The present behaviour, as described, is error-prone including risks much > exceeding the very small possible use of trying to adapt the "recognition" > to locales. (In reply to Aron Budea from comment #1) > ... > However, what if it was more apparent for the user how their input was > converted, or will be converted to a date? Is there a way to show this on > the UI? > > Dear UX team, can you consider ideas for making date input less error-prone? Sorry, but we provide the Tools -> Options -> Languages "Date acceptance patterns:" field to customize date input and CSV filtering in the exact format desired as alternative to--or in addition to localization default. Also, as noted the ISO 8601 formats are always honored. In sheet cells correctly cast to hold dates, the first example will enter correctly as 2015-05-30 if a date pattern of D-M-Y is added to the field. Likewise the second will enter correctly when a pattern of M-D-Y is present. Obviously, when working with data, user needs to be clear as to the filter/input pattern they have set. Meaning, it functions as intended enabling a user to manage their data and adjust from defaults for their needs, while providing reasonable localized defaults. Would say NAB and otherwise won't fix. From the Help: Date acceptance patterns Specifies the date acceptance patterns for the current locale. Calc spreadsheet and Writer table cell input needs to match locale dependent date acceptance patterns before it is recognized as a valid date. Default locale dependent date acceptance patterns are generated build time, but it is possible to add more or modify them in this edit box. Additionally to the date acceptance patterns defined here, every locale accepts input in an ISO 8601 Y-M-D pattern, and since LibreOffice 3.5 that also leads to the YYYY-MM-DD format being applied. Syntax: Y means year, M means month, and D means day, regardless of localizaton.
Thank you for the detailed explanation Stuart, set the status of the report as you see fit.
Happy to leave this open for now, see if there is further comment.
(In reply to V Stuart Foote from comment #4) > Happy to leave this open for now, see if there is further comment. Your explanation wrt. to the bug is thorough -> NAB. What I could imagine, however, is more transparency for the conversion process. AFAIR the cells with converted content gets a small visual indicator in Microsoft Excel. And perhaps sometimes the conversion is unwanted, so being able to undo would be nice. But these are other questions.
One more comment on the example "5-13-14" from my original report more than two years ago: In a locale (say English (USA)) "recognising" "5-12-14" as meaning 2014-05-12 an input of "5-13-14" MUST be interpreted as 2014-05-13. in fact this input is NOT recognised as a date at all but taken as text. The askbot question I mentioned in the original report was caused by this behaviour under English (USA) locale which is clearly a bug at least in the sense of being intolerably illogical. In a locale (say English (UK)) "recognising" "5-12-14" as meaning 2014-12-05 an input of "5-13-14" MUST NOT be accepted as text, but must result in an error message. There is no justification to regard the one input as a date and the other one as a text without notice. Inputters are fallible! I just tested the askbot case again setting my LibO V5.4.0.3 to English (USA) locale (under English (UK) user interface). I would suggest to not leave it at "Happy to leave this open for now, see if there is further comment." This is further comment - and I am not affected nor aggrieved personally.
(In reply to Heiko Tietze from comment #5) > ...(In reply to V Stuart Foote from comment #4) > > Your explanation wrt. to the bug is thorough -> NAB. > > What I could imagine, however, is more transparency for the conversion > process. AFAIR the cells with converted content gets a small visual > indicator in Microsoft Excel. And perhaps sometimes the conversion is > unwanted, so being able to undo would be nice. But these are other questions. Pointing to my above comment I object against "-> NAB". The huge field of "recognition" and related questions of formatting, localisations, and automatic conversion is completely messed up imo. This is a statement about spreadsheets genarally, not about LibO / AOO specifically. Will there be an opportunity to discuss the topic somewhere without writing reports, messages, comments, whatever most likely to no effect at all. It's all too narrow, and often misunderstandings cannot be expelled.
(In reply to Wolfgang Jäger from comment #7) > Will there be an opportunity to discuss the topic somewhere without writing > reports, messages, comments, whatever most likely to no effect at all. It's > all too narrow, and often misunderstandings cannot be expelled. You are very welcome to the design meetings, at the IRC channel, and on Telegram. https://wiki.documentfoundation.org/Design But your report is clear to me (as native German who prefers English UI it's always 'surprising' how numbers are converted). On the other hand, Stuarts reply is also reasonable. And we will not change how numbers are converted. And actually there are plenty of functions to deal with datetime. What I could imagine is better feedback. When you insert 5-1-15 and press Enter the text could fade into a grey M-D-Y (depending on how it works) that fades into the final Jun/01/20015 (or whatever). Eike, what do you think?
(In reply to Wolfgang Jäger from comment #6) > In a locale (say English (USA)) "recognising" "5-12-14" as meaning > 2014-05-12 an > input of "5-13-14" MUST be interpreted as 2014-05-13. I'd rather say accepting 5-12-14 as date 2014-05-12 in an en-US locale without modified date acceptance patterns (does not contain M-D-Y) is a bug. What is actually happening here is that the input first is recognized as "slightly possible ISO date input (where year would be 5)" but the end check rightly fails and then the en-US MDY order is applied and the date accepted anyway as 05/12/14, which is wrong. Similar wrong for other locales, eg. in de-DE the numbers are applied to a DMY order, resulting in 05.01.14, wrong again. For an input of 5-13-14 already the "might this be an ISO date" check fails because there's no month 13 and if the input does not match a date acceptance pattern correctly does not result in a date. Taking.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ae6c5083f04f8fad299cdc7a6b6558f1270cc619 Related: tdf#91758 don't remember nCanForceToIso8601 state It will be available in 6.0.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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6254fbf8a76a0d8edb290b03e0632ef6513a8d98 Related: tdf#91758 shortcut CanForceToIso8601() for MayBeIso8601() results It will be available in 6.0.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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1cfe6a904d3349413173f5d412c280936f938a9e Resolves: tdf#91758 more stringent check for ISO date like looking input It will be available in 6.0.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/41726 for 5-4
Eike, what do you think about more feedback? Something like the small red shape for comments e.g. at the lower right cell corner or just next to the comment. It would expand just like the comment and show the original input.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b0f759560b19b036751e8e557562de952c3da1af Correct a test that worked only by chance, tdf#91758 follow-up It will be available in 6.0.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.
(In reply to Heiko Tietze from comment #14) > Eike, what do you think about more feedback? Something like the small red > shape for comments e.g. at the lower right cell corner or just next to the > comment. It would expand just like the comment and show the original input. What do you mean, nagging about every input that resulted in string but could be something else if it was written differently? I don't think that's a good idea. Also, for a string result the cell already contains the original input as it was not converted to number/date/... Other than that, there's some RFE about introducing a nag box if values in a series (ie. contiguous cells in a column) are of different types, similar to what Excel has. This bug here is simply a bug in date recognition still being too lax in this case.
(In reply to Eike Rathke from comment #16) > What do you mean, nagging about every input that resulted in string... Optionally, and with means to remove the 'conversion comment'. It wouldn't be much nagging but yes, that's what I have in mind. Actually the problem here is not only to correctly convert but also to understand how values are processed. But anyway, if you are still not convinced and no one else joins me I'm fine with keeping the status quo.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f49ef5b771910384de0bc2d4c86ad3e0442b295a Force ISO date format again if ISO input, tdf#91758 follow-up It will be available in 6.0.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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9540916d824521592c08fdbff5014a01dffca752 Unit tests for tdf#91758 It will be available in 6.0.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-5-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=54bb595027197b6ffba9b95b2eafedefa03f3996&h=libreoffice-5-4 Resolves: tdf#91758 more stringent check for ISO date like looking input It will be available in 5.4.2. 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.