I just installed LibreOffice for the first time. I’m in Canada and my laptop is configured to use Canadian English and a US keyboard layout. I opened a spreadsheet created in Excel 2021 using LibreOffice. All the dates in the spreadsheet are displayed DD-MM-YYYY, which is of course basically useless. The only suggestion I could find to fix it was to change locale by going into Options>Languages and Locales>General>Locale Setting. I changed to US, but that simply swaps out the dashes for slashes. It still shows all dates as DD-MM-YYYY. Does anyone know to set it up so dates show in normal YYYY-MM-DD format? As is, this simple problem makes LibreCalc useless for me.
If your dates are really numeric values, then it is enough to reformat them with the desired pattern. Use a CellStyle for it.
But when you dates are pure strings, then you must convert then to numeric values before.
Can you upload an ODF type sample file here?
… from what?
For the “English (Canada)” locale, the default date format in LibreOffice is YYYY-MM-DD, as you would expect. What were your settings?
Note also, that if the file already has a cell format, then - well, it is shown as its author designed / intended. Changing format of cells in existing file is - editing it; there is no “do some magic, so that others’ files shown differently for me”.
Also, in this case, the “upload an ODF type sample” request is wrong. We would need the Excel-made sample, which shows the problem - if it was shown YYYY-MM-DD in Excel, but DD-MM-YYYY in Calc, it’s a bug.
Also: what LibreOffice version you use?
Thanks for taking a look. Accessing the prior Excel 2021 .xlsx file on my other laptop using Excel 2021, I see that the cells in question have numeric date values formatted as dates in Excel. They are displayed YYYY-MM-DD by Excel. Opening the file in LibreOffice (version 24.8.2.1 for X86_64), the cell instead displays as DD-MM-YYYY. The cell editor box shows up correctly as YYYY-MM-DD. If I press ctrl+shift+3 on the cell, the cell changes into the numeric value (45576 for 2024-10-11) . Pressing ctrl+shift+3 again, the cell shows up as YY-MM-DD. That’s closer than if I use the USA locale but how do I just make it YYYY-MM-DD?
The puzzle will be solved if you upload an Excel file with at least one problematic cell (and tell us the address of this cell). 
1 Like
My version
Version: 24.2.6.2 (X86_64) / LibreOffice Community
Build ID: ef66aa7e36a1bb8e65bfbc63aba53045a14d0871
CPU threads: 4; OS: Linux 5.15; UI render: Skia/Raster; VCL: x11
Locale: en-US (de_DE.UTF-8); UI: en-US
Calc: threaded
I switched the locale to “English (Canadian)” and entered =TODAY() into an unformatted cell with number format “General”. The value is displayed as 2024-10-25.
Then I switched to “English (USA)” and the value is displayed as 10/25/2024.
Back to German, I see 25.10.2024 in the same cell.
If you look at the status bar at the bottom of the spreadsheet, what language does it show?
If you click on one of the errant cells and press Ctrl+M to remove direct formatting, does it show the desired format?
1 Like
Unfortunately, this will ~never help with dates. There is no “date” data type in Calc, unfortunately; thus, there is no other way to show dates, than to apply explicit date number format - either directly, or through a style. When you enter 2024-10-26 in a default-formatted cell; or when you enter =TODAY() in that cell, - in both cases, calc analyzes the input (“the entered string happens to fit an accepted date pattern”) or the function metadata (“the function is known to return a date”), sees that the cell is default-formatted, and then it applies direct formatting to it, to show that date. Hitting Ctrl+M would make that cell show the likes of 45591.
1 Like
The straightforward way to format a cell in an existing sheet to YYYY-MM-DD is to
- Right click the selection (or press Ctrl+1) and select Format Cells
- In the dialogue that opens, select the Numbers tab.
- Under Category select Date
- Under Format, select 1999-12-01. You will see the Format Code YYYY-MM-DD or you can instead, enter the format code in the same place to avoid having to look for 1999-12-01 in the Format pane.
- OK
This procedure, apart from keyboard shortcut, is the same as Excel 2010 to format cells
The settings in Options give *new* spreadsheets default settings if they aren't created from another template. For instance, if I set the the options in my installation to the same as yours and type 1/2/24 in a cell, when I press Enter it automatically changes to 2024-01-02
For me, selecting the column of dates from an Excel sheet and pressing Ctrl+M to remove all direct formatting and then clicking on date icon, or pressing Ctrl+Shift+3, converts the dates to YYYY-MM-DD