Created attachment 178687 [details] "Date Acceptance Pattern" For English (USA) I gather this is a complex issue with a maze of possible solutions... But it seems there is one specific change that would have eliminated the problem that wasted hours of my time. Perhaps it belongs under "Localization" instead of Calc, but it is definitely an issue in Calc. As mentioned in https://ask.libreoffice.org/t/how-do-you-disable-date-formatting-in-libreoffice-calc/38943/4 and illustrated in my screenshot included here, the default "Date Acceptance Pattern" For English (USA) includes "M.D". That seems to mean any entry that could be a month (1-12), followed by a '.', and then a number that could be a day (1-31) will become a big date code. Even if you intend it to be a dollar amount that isn't formatted with the '$' prefix. I've spent my whole life in the USA, and have never seen anyone assume 12.08 was the eighth of December! I know it is done that way in some languages, but not in Default English. Removing that "M.D" entry fixed my problem, despite its possibly tricky origin. If you want thee whole story: Fix imported text of amount with ‘$’: =MID(C2,2,9) (last number of chars parameter can be longer than available input) $11.81 becomes 11.81 (But actually '11.81) BUT… You end up with text that can't be reformatted to numbers!! You have to do the Data->Text to Columns trick (selecting Standard) on the (preceded by ') text to get it to reformat. (That did not work directly on the ‘$’ versions of the amounts.) BUT… A few rows ended up weird: MID result, Text to Columns result 10.21 44855.00 4.24 44675.00 12.08 44903.00 That's 4 of 159 rows… I could manually delete the wrong number and type in anything that could not be an MM.DD date. Type the proper number and the bizarre value returned. Sometimes not right away… Those are the only rows that could be interpreted as MM.DD; rows that could be DD.MM were not a problem. --> This was still present when pasted to a totally new sheet! No efforts to remove formatting would stop it. https://ask.libreoffice.org/t/how-do-you-disable-date-formatting-in-libreoffice-calc/38943/14 Go Tools>Options>Language Settings>Languages>Date acceptance patterns and remove all the patterns you don’t want to be used when trying to “recognize” input as a possible date. You may also enter a different pattern if you still want automatic recognition based on something uncommon. The only (implicitly preset) pattern you cannot delete is ISO-8601 delimited with 4-digit-year. (The month and the day also are accepted in 1-digit abbreviation). You also should not leave the respective input completely empty. If you did the settings would be replaced by the defaults again. You may put in something like D..M.. what will not actually occur. (Yes. It’s a mess.) In case your test doesn't find this problem, I've included a new test sheet with the problem rows pasted in. It shows the problem here.
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.