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.
