Hello. In Options →"Languages and locals" → General → Formats → “Accepted date formats”, I can set “Y-M-D” but in the formula bar, LibreOffice keeps converting dates into my local format (French) like this: “16/05/2026 08:23:00” then doesn’t recognize this as a date! I’d like the ISO format “2026-05-16” to be used in the formula bar too.
Set the locale to French (Canada)
[/Edit]See also my comment to the answer by @ady.
I my assumption explained there is wrong, please tell me.[/Edit]
3 Likes
Thank you very much. In fact, the problem was that I thought different type formats should be separated by a comma like this: “Y-M-D,D/M/Y”, which is not accepted so I ended with entering only “Y-M-D” but then, when I type “2026-05-16”, LibreOffice Calc keeps turning this into “16/05/2026” according to the locale and then… doesn’t recognize its own format as a date! because it’s not of the form Y-M-D. I regard this as a bug: if the only recognized format is Y-M-D, then Calc shouldn’t turn such formatted dates into the locale. Moreover, a tooltip giving the the separator (semi-colon) would be appreciated.
How do you enter the dates?, pasting from another place?, you just type it?
Or, just change the cell number language to French (Canada).
Pasting or writing 16/05/2026 08:23:00 in such a cell gives me that.
And in the status bar.
Let’s figure out together what happens when you enter the character sequence 2026-05-16 into a cell and end data entry mode.
The result depends on the cell’s number format (at the time you started typing).
If the cell has text format, the cell value will be the string 2026-05-16.
Otherwise, the entered character sequence will be recognized as a date (for any locale) and the cell value will be the number 46158 (the ordinal number of the day from the “zero” date 1899-12-30).
Additionally, if the cell had the “Standard” number format at the time the data entry was started, the cell’s number format will be changed to YYYY-MM-DD.
The situation you described may occur if the cell has the DD/MM/YYYY format at the time the data entry starts.
There is no point in adding this string to the described parameter, since ISO dates are already recognized for any locale.
If these explanations are not sufficient, please upload your file for further research.
1 Like
This is the behavior here with Calc’s standard settings.
LO 7.3.7.2
OS Linux Mint 21.3
Locale: en-US (en_US.UTF-8); UI: en-US
Please clarify the question.
We were looking at a case where a date is entered without a time component.
Both.
Behavior = you stated above.
1 Like
Type-in the date-time in ISO format, such as 2026-05-16 08:23:00. It works for me without having to change my Locale nor my Date acceptance patterns.
1 Like
I would assume the questioner was able to enter a date in ISO format, but they wanted to also get shown a date which was entered in advance into a cell in ISO when the cell was selected - in the formula bar.
Not too old versions of LibO Calc always accept as a date YYYY-MM-DD entered into the formula bar or into a cell opened for editing independent of the locale.
The way the question was asked, I had the impression that the date-time was introduced (typed-in) as 16/05/2026 08:23:00 (i.e. not the ISO format) and only then the number format code of the cell was edited. This is different than typing-in the date in ISO format in the first place. That’s why I said that, when directly typing the date in ISO format, I do not experience the difference described in the question.
If the date-time values are all already introduced, I guess that they could be copied elsewhere, eliminate direct formatting, and use a function to take the internal serial number so as to present it as the desired ISO format (instead of changing the number format code of the cells). The result of the formula could be copied again and paste it as values+format where it is needed. I have not tested this.