As far as I have read calc uses OS short date format. Mine is set to dd.mm.yy. However Calc displays 4-digits by default. How can I force Calc to respect system short date format? Win 11 25H2, LO 25.8.3.2.
I can find topics where people want to use different date format than OS default. I would like Calc to work as expected. I have many columns and year with 4 digits displays ###. And I cannot make the column wider because then I cannot fit all necessary columns on screen.
Not for me.
OS date config:
In each case, I wrote 14/11/2025.
No. By default, it uses the locale’s date format, which is defined as default. It is really two-digit-year for most locales; but it’s not “Calc uses short date format”, but “most locales happen to specify short format as default” - and that may change any day for any given locale. And some examples of locales using long format are Bulgaria, South Africa, Canada… I bet, your “problem” comes from using such a locale.
Calc doesn’t respect system short date format
…
Mine is set to dd.mm.yy.
…
I can find topics where people want to use different date format than OS default.
LibreOffice does not use system date format. At all. There is a request to allow that (tdf#46448, tdf#73242); but currently, there is no such feature. What LibreOffice uses is its own locale data.
Then the info I read from another topic was incorrect. Is there a way to change the locale data? Somewhere in config files?
I have now tried previous versions and it seems that Libreoffice 6 is the last one to show 2-digit years. If there will be no other suggestions then I suppose solution is to use an older version of Libreoffice.
I can’t imagine a “reasonable reason” ...
Dates with 2-digit year aren’t actually dates. They leave a most relevant fact to guessing indstead of giving the information.
Use YYYY-MM-DD (ISO 8601).
Old versions have bugs which are fixed in newer versions, and they may lack useful enhancements.
Nobody can reproduce your problem. The attached spreadsheet has been generated to show all locales of version 25.8, including a column of dates displayed in the default format of the respective locale. I can spot 4-digit years in Islamic calendars and Canada’s ISO format.
Locales_LO25.8.ods (43.9 KB)
If I write “1.11” in a cell, then with Estonian locale I get “01.11.2025”. Which locale should I choose to get “01.11.25” like in LibreOffice 5?
Yes I know I can change format, but is too time consuming to change every time when importing csv or pasting from other applications.
The cell contains value with 4-digit year. I just don’t need it to be displayed with 4 digits. I don’t deal with dates before 2000, so the two first numbers just take extra space but give no additional information.
Rather than choosing another locale which might cause problems for you, Estonian spell check, etc., it would be better to create a blank document with a cell style, e.g. 2 Digit Year, with the date format that you want. Save it as Default Template and that cell style will always be available in the Sidebar. Simply a matter a of selecting the range, or column and double clicking the 2 Digit Year cell style
That’s the whole point not to do it manually every time. I’d have to do it hundreds of times per day.
While pasting data I often have numbers and dates on the same column. LibreOffice 5 correctly identifies dates and changes format of only those cells. Manually I’d need to select the cells and ranges individually which would take several minutes even with couple hundred rows.
Also what benefit does creating a template have over just making a new cell style?
Is there a way to edit locale settings in config files?
Including the cell style in the default template makes it immediately available to new documents.
The setting is in Tools - Options - Languages and Locales - General. Choose a locale with a 2 digit year format and period as separator. Note that this might change decimal separator to a comma.
If you choose an existing locale that has two digit years, e.g. New Zealand or UK and are content using a forward slash as separator instead of periods then that is it. Enter 1/11 ad it will become 1/11/25
No, I need dot as separator. I tried Openoffice, it has “dd.mm.yy” as default date for Estonian locale. Unfortunately it doesn’t have IFERROR function that I also need. Perhaps I can copy OpenOffice locale files to Libreoffice folder?
You could try German (Switzerland) but you need to enter the full date, e.g. 1.11.25 or it remains a number because the period is also the decimal separator
The change was in tdf#130154.
Somebody using the point as the decimal separator can use the comma as the separator between day-digits and month-digite setting the Date acceptance pattern D,M
Also a way: Personally I use D.M..
For curiosity: Can you explain why?
@Lupp: likely because that’s the national standard, as shown at List of date formats by country - Wikipedia.
You can only recompile LibreOffice. The locale data is not represented as a configuration.
If this is a problem, there might be a macro that will instantly change the “dd.mm.yyyy” format in sheet cells to “dd.mm.yy” (or vice versa).
The separator used in a date recognition pattern doesn’t change anything concerning the formats set for displayed dates.
[Still: YY for the display in cells and for printing is bad.]