| Summary: | Calc (USA English) interprets numeric input of "12.08" as the eighth of December if the user added a date acceptance pattern of M.D | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Loren Amelang <loren> |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | 79045_79045, erack, loren |
| Priority: | medium | ||
| Version: | 3.6.2.2 release | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:7.5.0 target:7.4.3 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: |
"Date Acceptance Pattern" For English (USA)
The problem lines pasted to a new sheet |
||
|
Description
Loren Amelang
2022-03-07 01:52:15 UTC
Created attachment 178688 [details]
The problem lines pasted to a new sheet
Eike, what do you think about it? (In reply to Loren Amelang from comment #0) > illustrated in my screenshot included here, the default "Date Acceptance > Pattern" For English (USA) includes "M.D". No, it does not. The default English-US date acceptance patterns are M/D/Y;M/D Obviously specifying a user date acceptance pattern M.D if the decimal separator is '.' dot is a bad choice. What is expected in this case? Some magic? As a remedy we could forbid entering a date acceptance pattern that matches a decimal separator value. Eike, I just checked my Arch Linux installation of 7.4.0.3, and it matches your "M/D/Y;M/D", which is obviously correct for US English. So how did my Windows installation get to include "M.D"? Maybe at some point since 2015 when I first installed Libre on this version of Windows, the "M.D" was included in the default, maybe accidentally, and it stuck? Maybe some system crash imported the "M.D" from another language default? Is there a language with a default of "M/D/Y;M/D;M.D" ? Could opening and saving a file in Excel or Collabora Office on iOS somehow propagate the different default into Libre? I didn't even know the setting existed - can't believe I intentionally changed it! Glad to hear the problem is not affecting other people. So I did some searching, and found a trail about a year old - where I _did_ change the default! Deep in a late night rabbit hole, following these instructions: https://ask.libreoffice.org/t/how-to-format-the-date/43077/6 ----- At “Date acceptance patterns:” you can see what entries are available. You can add your own. You can format cells for a date by going to Menu Format>Cells. There you select date. You can also specify a custom user-defined format at the bottom of the dialog box. ----- I'm a "YYMMDD" person. I wanted a way to enter dates with that format. Libre won't accept MMDD or other systems without non-numeric delimiters. "M.D" defaults to the current year, and was the closest I could find. But my lizard brain hated it, and I forgot I'd added it. The problem in this report turned out to be that it affects not only currently typed dates in date columns, but the handling of any number in any column that could possibly be an "M.D" date! Like dollar values... In columns that are not date formatted! I found I could choose "YYMMDD" as a display format for date columns: 220115 515.71 inet With Col.1 formatted as date, that shows 01/15/2022 when selected, but displays YYMMDD when not selected. Entering Ctrl+; works! Shows MM/DD/YYYY when selected, but displays YYMMDD when not selected. But entering "221010" in a column of similarly formatted dates shows 02/06/2505 selected, and displays 050206. Maybe it is just me, but that seems wrong! If a column is explicitly set to dates, you should be able to enter more dates formatted like what you see above your cell. And even more importantly, in columns that are NOT formatted as dates, the "Date acceptance patterns" should not affect the interpretation of numbers at all! I imagine changing this would create even more problems for people who are accustomed to this behavior. But I certainly don't understand why Libre applies date acceptance patterns to pre-existing numbers in non-date columns! Is there some separate mechanism for choosing only how an entry you are currently typing will be interpreted? (In reply to Loren Amelang from comment #6) > The problem in this report turned out to be that it affects not only > currently typed dates in date columns, but the handling of any number in any > column that could possibly be an "M.D" date! It's treated like every other date input because it was specified as date acceptance pattern. This M.D pattern makes no sense in your locale but was forced when added. Hence I say it shouldn't be accepted in such constellation. > Like dollar values... In > columns that are not date formatted! Like any other date input. A display format is not an input mask. > If a column is explicitly set to dates A column is not set to dates. Cells of a column may have number formats applied, that display the underlying numeric value as date. Just enter 2022-10-22 in a cell, then remove all formatting with Ctrl+M and see the value 44856. > you should be able to enter more dates formatted like what you see above your cell. You format values to not look like dates but numbers and at the same time want the input of a number be accepted as date.. again, number display formats are not input masks. > And even more importantly, in columns that are NOT formatted as dates, the > "Date acceptance patterns" should not affect the interpretation of numbers > at all! If the user told it to do so.. again, such M.D pattern simply should not be allowed in a locale that uses the . dot decimal separator, or if it was encountered in the configuration be ignored/discarded. > Is there some separate mechanism for choosing only how an entry you are > currently typing will be interpreted? No. Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/cf429e79367775a03c2ca89ed57d3de88bd2c6dc Resolves: tdf#147817 ignore date acceptance pattern with numeric ambiguity 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/+/141706 for 7-4 Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/cdded98b23698e5e2a5cfffd5638e395098fcc4a Related: tdf#147817 prevent date acceptance pattern with numeric ambiguity 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/+/141686 for 7-4 So does
{
SAL_WARN("svl.numbers", "ignoring date acceptance pattern with decimal separator ambiguity: " << rPat);
continue; // for, next pattern
}
actually appear as a warning to the user? That would be very helpful!
Thank you for dealing with my naïve misunderstanding of where and how pattern recognition works.
(In reply to Loren Amelang from comment #12) > actually appear as a warning to the user? No, it's a debug build message. Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/648c70ac2caf2646ee8ff49bd8d846016d289b38 Related: tdf#147817 Extract validateDatePatterns() from DatePatternsHdl() 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. Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6d2363a553c9e275f9430510d70bc4b84e02aad8 Related: tdf#147817 validateDatePatterns() also when (re-)setting/initializing 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. Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/29885b7723d49a78b123a2f1a19aa54df4cef9fc Related: tdf#147817 prevent date acceptance pattern with numeric ambiguity It will be available in 7.4.3. 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-7-4": https://git.libreoffice.org/core/commit/1e7c707282d28e60c7667e4c4dcdb9b2348fe862 Resolves: tdf#147817 ignore date acceptance pattern with numeric ambiguity It will be available in 7.4.3. 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. |