Dear all,
my LO locale setting is set to English UK.
If I create a new document and enter today’s date, I get is this:
08/02/2020
which is exactly what I want
However, I have an old file where if I enter today’s date anywhere, I get the American format:
02/08/2020
How is this possible? Is there a locale setting specific per file? Used to be ok until yesterday and this is driving me nuts.
Thanks
Hello
How is this possible? Is there a locale setting specific per file?
It is even possible to have a locale setting per cell - see Format -> Cells -> Tab: Number under Language rightmost, which can be set on a per cell basis)
Here a screenshot, where different language settings (with their respective default) for a few cells are used:
And this is possible, since a date is stored as a number and a format in its locale is just a specific representation of that number (specifically: A date is the number of days past since 1899-12-30, which is day 0 - see also setting in Tools -> Options -> LibreOffice Calc -> Calculate -> Section: Date -> [o] 12/30/1899, which is defining day 0 )
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
Thanks for your reply. Unfortunately this is not the case here. If I create a new sheet with no formatting at all, the date is still displayed in the wrong format, meaning that this has nothing to do with a specific cell formatting.
As I mentioned, everything was fine until yesterday then something got messed up sheet-wise.
I did not provide a solution, but an answer to your actual question “How is this possible? Is there a locale setting specific per file?”. If you want another answer, ask another question and ask e.g. “How to fix”.
While Opaque correctly answered you actual question - “How is this possible?” etc. - I suspect that what you meant to ask for was a way to change your file so it displays dates in a sensible format according to your locale.
There may be a quick fix, but it depends on a structured approach to formatting. If your file has direct cell formatting which touches language/locale settings, this is likely to fail. Likewise if your file has visited “Excel context” (been edited in Excel, content copied from an Excel sheet, or at some point been stored as an Excel file (xls/xlsx/xlsm).
The easiest fix is to change the root cell style, “Default” (depending on your language settings, this style may be renamed to a different term, probably “Standard” or something in that direction).
F11 or menu Styles - Manage styles)Hierarchy from the “views dropdown” in the bottom of the toolbox. The root style should now be the topmost style in the list.Modify
Numbers tabLanguage dropdown where you can select the appropriate language for your desired date formatting. Pick a language and click OK
If that doesn’t help, you can try to select all cells (ctrl+A) and apply the language directly through the menu item Format - cells instead of by style. This should overcome direct formatting and Excel context issues. The downsides are that you may need to do it sheet by sheet, and it may have side effects if there are cells which should have a particular language designation other than the one you default to. In most cases you won’t face any significant problems with this approach either.
Thanks, that did the trick. I have never opened this with Excel but I do have opened the file with an older version of LO on windows, maybe that has caused the issue?