Regional and Libre Calc date settings

[pleasehelp]

Hello, Please help me, i am at my wits end

Background setting: I am in South Africa, we write the date as DD/MM/YYYY or variations thereof, but the month is in the middle. I also often need to work with CSV files. My operating system is Linux Mint 20.3 cinnamon 64-bit. Libre version 6.4.7.2

Please advise what my regional settings and then my Libre calc number (Date) settings should be.

I have tried various options and sometimes it comes right for one cell, but then the vary next cell jumps to the USA default, even if I have highlighted the whole column or row. Also when I close the file after I have individually changed cells, it often (not always) reverts back to the default USA format. Sometimes in the cell format settings it shows I have my chosed format, but it displays different and other times I can write it as a “text” with the (’) in front, but then the sorting by date function cannot be used. It does not make sense to me at all. Please please can someone help me out of my misery

This is very frustrating and confusing when one works with other documents and information is shared. The attached document is an example of what I mean.

Lani slips date frustration issue.ods (20.5 KB)

[karolus]

→Data→→Text to Columns

[Lupp]

It’s a bit hard.

1. South Africa officially made ISO 8601 (YYYY-MM-DD) a standard, but many people either won’t know or won’t worry but keep their personal traditions.
2. Recent LibreOffice with locale setting English (South Africa) respects the standard, and so do cells having set English (South Africa) as “language” under the tab Numbers for formatting. (LibO V 6.4. seems to not yet follow that standard. My 7.4 does.)
3. Concering CSV you should in any case export dates exclusively in ISO 8601 (with dashes) format. Everything else is hazardous concerning the result somebody elsewhere may get when importing your data without proper settings. Don’t try to mimic somebody elses settings.
4. If you don’t use the mentioned locale, or your version doesn’t treat the current default correctly, create a cell style with the Numbers format YYYY-MM-DD and apply it to all the columns expected to get dates as cell contents or results.
5. Importing from CSV you need to first be sure what format the sender actually used. Then choose Detect Special Numbers in the import dialog, and select the proper sequence for the column header in the import dialog. That’s nearly the same as @karolus described (image with German marking).
6. Dates not expected to be used as operands in calculations are perfectly safe if you treat them generally as texts regarding the mentioned ISO standard. ISO itself doesn’t tell anything about the numeric (as a single number regarding any nulldate or epopch) representation of dates. That’s spreadsheet stuff.