Locale and language settings confuse DD/MM date input

[AylaAsperger]

I am using OpenOffice in English, with the locale set to Dutch. When the language is also set to Dutch and I type 1-4, it is interpreted as the 1st of April, however it will use Dutch month names. So I changed the default language to English, but the Locale is still set to Dutch. It now properly displays dates with the English month names, but now it starts to interpret 1-4 like the 4th of January. When I am looking this up on google it tells me to change my locale to the proper setting, but how do I get it to recognize typed dates as d/m without changing the whole thing back to Dutch? Any tips are greatly appreciated!

[Villeroy]

In the language settings there are a user interface language, a locale for numbers and a language for written language (spell checking, typographic features). The locale is the default locale for all numeric expressions where nothing else is specified. It also determines the rules for text to number conversions. Constants in formulas always use this locale. The locale can be overwritten on cell stlye level and cell level. The setting to override the global locale is the setting which is misleadingly labeled as "language" in the number format dialog. The written language can be overwritten in the font dialog.

OpenOffice date recognition works as follows:
0) A number is recognized independent from any formatting except that number format "Text" supresses any recognition.
1) ISO dates (1999-12-31) are recognized as dates independently from any locale setting.
2a) 1/ enters this month's fist day
2b) 1/4 or 1/4/ enters this year's fist of April or 4th of January in US context.
2c) 1/4/5 enters 2005-April-01 or 4th of January 2005 in US context (1 or 2-digit years <30 are in this century. there is also a setting for that).
3) There are numerous additional ways to enter dates with month names or with other separators. These ways depend on the locale.
This is how things work in OpenOffice. Many users were competely overstrained by these simple rules which is why LibreOffice introduced a "date recognition pattern" on the language settings dialog were you can specify the patterns you want to be recognized as dates (in addition to ISO dates which will always work). I gave up on OpenOffice but my date recognition pattern is explicitly set to d/;d/m;d/m/;d/m/y which implements the normal date recognition I descibed under 2).

[RusselB]

What I do to keep that confusion from happening is to use the month name, rather than the month number.
Example: 3-Jan rater than 3-1 or 1-3 (depending on the specific locale). Note: if using the month name, you'll need to use it in the correct language, I believe.

[AylaAsperger]

So if I understand it correctly: In LibreOffice you can edit the way it recognizes input, and in OpenOffice you can just select the way it LOOKS, or type a whole lot more characters (which is a pain in a large list of data), or have it set to the wrong language. If my understanding is correct then I hope this will somehow end up in a future version as a possibility, because having the system you are used to should not in any way limit in which language you want your data to show up. IMHO. Thanks for the input.

[Villeroy]

Simply use 31/ or 31/12 or 31/12/99 and format the resulting dates any way you want before or after the input. There is no shorter input method. Yes in LO you can define alternative patterns.

I enter 25/ into a preformatted Dutch cell and get: vrijdag 25 december 2020 as a formatted numeric value. The true value is 44190 (days since day zero 1899-12-30)

[AylaAsperger]

The thing is, I've had OOo set with Dutch as a default language for a while, and that worked flawlessly with inputting dates like 1-4 as the 1 april. Due to circumstances I need to switch to mainly English documents, so I was very surprised to find all my dates being turned around in a way I am not used to, with 1-4 becoming the 4 january. I know I can edit how they 'look' afterwards, I was just looking to see if there was anything to streamline the input. And I was surprised as help pages say to change the Locale and that is still set to Dutch, so therefore I wanted to ask if there was any solution. If there currently is none, then I can just say what I said in my previous post, that I hope in future updates the way of input is put back in to Locale and properly separated from the chosen language.

[Villeroy]

I have no idea what happened to your spreadsheet or with Calc compared to ancient versions of OpenOffice. Either use the slash input with OpenOffice or install and customize LibreOffice.

[AylaAsperger]

Nothing except what I wrote above this. I switch the primary language for documents from Dutch to English. As I mentioned in my replies above, I understand it is currently not possible, but I just hope a future version might have a fix for this.

[Villeroy]

The "default language for documents" does not affect any numbers.
Language settings include
1) User interface
2) Locale (default context for numeric expressions such as dates)
3) Language (default context for human language, words or symbols)

[AylaAsperger]

This setting, default languages for documents, is the ONLY thing I changed. And it actually DID change what dates where interpreted as.

Default Language set to Dutch = 1-4 = 1 April
Default Language set to English = 1-4 = 4 January

So it does actually make a difference in how dates are interpreted, because I changed nothing besides this single setting, and Locale is still set to Dutch.

[Villeroy]

I can't reproduce this with OpenOffice 4.1.8 on Linux. I set locale and language to Dutch (Netherlands), enter 9-12 and get 9th of December. This does not change when I switch to another default language and start a new document with English language.

[AylaAsperger]

I have completely removed 4.1.7 and installed 4.1.8, and did some testing. This is what I found:



It turns out that the way the typed date is interpreted is changed when the language of the number has been changed. For these docs I need the months to be shown in English, however it seems to be that there is no way to choose the Dutch type of input with the English words at this moment. If any Dev sees this and has an idea what to do with this: Many thanks! If not, too bad, but I understand. Thanks for all the time!

[Villeroy]

Everything has been said. Use slashes or install LibreOffice and define your own date recognition pattern. Slashes simply work.

[AylaAsperger]

Everything has been said. Use slashes or install LibreOffice and define your own date recognition pattern. Slashes simply work.

I know, that's why I said multiple times that I acknowledge there is probably no solution at this moment. I'm not sure why that wasn't clear. Also, on a sidenote, if you type 1/4 you get a 1/4th, and if you type 01/04 you still get 4 January. So that's no fix either. Either way, there is no solution right now, so I'm not expecting any fix coming further from this threat, unless a future version maybe adds more options. Thank you for all your time, and have a nice evening.

[Villeroy]

Yes, this is all inconsistent. Spreadsheets accept any data type in every cell. It is far easier to adjust your huaman brain than to adjust the software to do the right thing in every situation. It could be very easy if the program would accept ISO dates and point decimals only and if it would not auto-correct anything. But then millions of people could not use the program.

[AylaAsperger]

Very true! One last question, slightly off-topic: On my last screenshot I see all kinds of dates with 'Tevet' on them. I'm not sure which calendar that's from, as it is certainly not English. Is this baked in to the program or is this a plugin that you can uninstall somehow? Thank you very much for all your help volunteering on this forum!

[Villeroy]

That's Hebrew.

[AylaAsperger]

Okay, but it is located in 'English - USA' and I don't have Hebrew language packs, why would that be?

[robleyd]

Seems selecting the US English language enables the Hebrew options. They aren't available for instance if I select UK English, or my default of Australian English.