DD/MM/YY date format not respected in Calc cell

[shimonl]

Hi,
I have the following problem.
In a spreadsheet, I am entering receipts: recipient name, ID number, sum, and date, where the date is supposed to be in DD/MM/YY format. With right click, “format cells”, I clicked on Category: date, and in the Format Code box I entered DD/MM/YY (replacing MM/DD/YY which originally appeared).

The format example now showed up as 31/12/99, and I clicked OK.

Now, if I type in that field “02/03/20” (March 2), when I press enter, the cell shows “03/02/20”, right aligned. Similarly, if I type any numbers which also comprise a valid MM/DD date, the cell fills with DD and MM reversed.

But, if I type a date which is only valid as DD/MM/YY, such as 13/10/22, it appears LEFT ALIGNED in the cell, as text.

Additionally, any numbers which could be valid as MM/DD get padded, e.g. I typed 1/5/90, and 05/01/90 appeared in the cell. But when I typed “17/4/95”, that’s exactly what the cell showed (left aligned), with no padding.

SO: Calc seems to be totally ignoring the fact that I defined the cell as a DD/MM/YY DATE, and wants to insist on MM/DD/YY.

As a very weak user of LO, I can only assume the error is somehow mine, and would very much appreciate help in doing this correctly.

Version: 24.2.3.2 (X86_64) / LibreOffice Community
Build ID: 433d9c2ded56988e8a90e6b2e771ee4e6a5ab2ba
CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: kf5 (cairo+xcb)
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

[edit]
It occurred to me that maybe the “language” of USA English, “forces” MM/DD, so I changed the field to Language UK English. The default format now showed DD/MM/YY, but typing 2/3/99 still filled the cell with 03/02/99.
[/edit]

Thank you!
Shimon

[Villeroy]

For the whole office suite, Tools>Options>Languages>General and switch from locale “English (USA)” to some other English.
For the current document, edit cell style “Default” so it has a non-US locale on the number format tab.
For the entire Calc component, edit cell style “Default”, save the file via File>Template>Save and make the template your default template via File>Template>Manage…

With the right locale, D/M/Y input will result in DD/MM/YY output, even if no number format code is applied

[erAck]

Check your Date Acceptance Patterns under Tools → Options → Languages and Locales → General, Formats, if they are contradicting what input gets accepted in which DMY vs MDY order.
See online help Languages (Options).

[shimonl]

Thank you.
Is there some way to change all the dates I already entered as plain text (left aligned DD/MM/YY) to be changed to actual DD/MM/YY dates? After changing the suite locale to UK English, and the default style as well, I hoped that my text dates would move to the right as dates. However, even though in the cells I see a DD/MM/YY date (such as 31/01/18), in the text box it shows an extra apostrophe: '31/01/18

[Villeroy]

@shimonl

However, even though in the cells I see a DD/MM/YY date (such as 31/01/18), in the text box it shows an extra apostrophe: '31/01/18

This is because a formatting attribute must not change any value. This is a feature. The apostrophe indicates that the numeric expression is literal text. Likewise, '=A1+A2 is not interpreted as a formula, and displayed literally without the apostrophe tag.
Make sure that your locale context is set properly, so 1/2/24 is interpreted as first of Februrary as intended.
Edit>Find&Replace…
Search: .+
Replace: &
Check “Regular expression”
Click “Replace All”
This effectively re-types all cell contents.

[shimonl]

Thank you so much!!

[shimonl]

One thing bothers me.
I understand that the DEFAULT date format, for a USA locale, is MM/DD, but if I EXPLICITLY set a cell to DD/MM format, for whatever reason, why should Calc be allowed to ignore that?

[Villeroy]

@shimonl

why should Calc be allowed to ignore that?

Because input and output are not the same. OpenOffice and old versions of LibreOffice followed the global locale setting overridden by explicit cell formatting. Thousands of users could not get their heads around simple rules, so LibreOffice introduced “date acceptance patterns”. They allow each and every user to set up whatever they want.