https://bugs.documentfoundation.org/show_bug.cgi?id=147817
Bug ID: 147817 Summary: Calc (USA English) defaults to interpreting Dollar amount "12.08" as the eighth of December Product: LibreOffice Version: 7.3.0.3 release Hardware: All OS: All Status: UNCONFIRMED Severity: normal Priority: medium Component: Calc Assignee: libreoffice-bugs@lists.freedesktop.org Reporter: lo...@psychoros.com Created attachment 178687 --> https://bugs.documentfoundation.org/attachment.cgi?id=178687&action=edit "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. -- You are receiving this mail because: You are the assignee for the bug.