Libreoffice calc disable auto date formatting

[COKEDUDE1]

I am trying to put dates similar to 01/21/2022 in my libreoffice calc file. Unfortunately it keeps getting changed to to 01/21/22 and changes the alignment to the right. Is there a way to avoid this? I would like it to stay 01/21/2022 like I am putting it in. Also not change my alignment.

I did see this idea but unfortunately I am copying and pasting so this will not work for me.

How do you disable date formatting in LibreOffice Calc?

The truest answer is that no, you cannot disable it. Thank you very much Mike Kaganski for the work around which is summarized here: Highlight and right click the cells you want to leave unformatted. Click “Format Cells…” Click on the tab “Numbers” Under the category select box click “Text” Click “Ok”. Your text should no longer be interpreted as date/decimal/currency etc for the selected cells. A few caveats. If you insert a cell then the LibreOffice Calc will revert to it’s old behavior fo…

The truest answer is that no, you cannot disable it. Thank you very much Mike Kaganski for the work around which is summarized here:

Highlight and right click the cells you want to leave unformatted.
Click “Format Cells…”
Click on the tab “Numbers”
Under the category select box click “Text”
Click “Ok”.

Your text should no longer be interpreted as date/decimal/currency etc for the selected cells.

A few caveats. If you insert a cell then the LibreOffice Calc will revert to it’s old behavior for that cell. This includes dragging and dropping the contents of a cell to somewhere lower in the document. The spaces made will revert back to the old behavior.

[robleyd]

Cross posted at Libreoffice calc disable auto date formatting (View topic) • Apache OpenOffice Community Forum where a solution has been offered.

If you cross post, please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

[Villeroy]

It would be “auto date formatting” if the program would constantly change the number format according to your input method. Either you format cells or you don’t. If you don’t and your input is recognized as a date, some default date format applies. The most important thing is that the entered value is correct. Formatting (color, font, border, number format) never changes the cell value.
How to enter dates into a spreadsheet (Excel as well) easily:
1/ enters the first day of current month
1/2/ enters this year’s fist of February or second of January in US context
1/2/3 enters 1st of Feb (or 2nd of Jan) of year 2003. By default the year 1930 marks the threshold to decide if a one or two-digit year falls into 20th or 21st century.

Regarding the alignment, a spreadsheet aligns numbers to the right border and text to the left until you force it to do otherwise. Either you format cells or you don’t.

[schiavinatto]

With the cell selected, menu Format / Format Cells under Category Date, in Format Code MM/DD/YYYY.

[Villeroy]

@schiavinatto

With the cell selected, menu Format / Format Cells under Category Date, in Format Code MM/DD/YYYY.

This is poor advice.

• Apply some non-US locale in Tools>Options>Languages>General
• Apply US date format MM/DD/YYYY
• Enter 1/2/25 → 02/01/2025 which is 2nd of January 1st of February and rather confusing.

For the whole office suite, apply your preferred locale in Tools>Options>Languages>General.
For all Calc documents, create a new document, change the number format locale of cell style “Standard”, store the file as a template and make it your default template.
For one Calc document, change the number format locale of cell style “Standard”.

[feedback]

There should be an option to turn off absolutely all automatic changes. All of them. I do not want my machine to think for me or to think what is best for me, I want everything I input to stay absolutely the same how I put it in. If I won’t like something I’d change it. A simple switch “disable all auto-corrections and auto-formatting” is very much needed. I really had a wave of rage in me just now with this meaningless and unturnoffable automatic format change. Modifying every document I have, modifying cells’ format in every document because I can’t do a general settings turn off is extraordinarily bad.

Automatic date switch from e.g. 06/05/26 to 05/06/26 without user consent is a very big deal, had I not noticed it I’d have been in a lot of trouble.

[schiavinatto]

Hi @feedback

[mikekaganski]

@feedback

A simple switch “disable all auto-corrections and auto-formatting” is very much needed. I really had a wave of rage in me just now with this meaningless and unturnoffable automatic format change.

… and you require something, that would hide a problem from you, instead of thinking what is wrong on your system.

@feedback

06/05/26 to 05/06/26

This kind of change definitely indicates a problem in configuration. You type something, and settings of your LibreOffice tell it to take it in one way as a date; and then it shows the detected date, where something tells it to output date parts in the other direction. And you better learn what is that, and enable yourself to manage that, instead of requiring something, that would turn Calc (a tool designed to grind numbers) into a dumb and passive clone of sheet of paper. Maybe your date acceptance patterns don’t really match what you would actually want (as @schiavinatto suggested)? Maybe your sheet (or range) is formatted not as you need (e.g., explicitly requiring some locale that you are unaware of)?

There already are means to disable automatic changes. Basically, you only need two changes to make that happen:

1. Disable AutoCorrect (so that it doesn’t happen at input);
2. Format your cells as text (so that it disables number recognition at the end of the input).

But if you did that, and it would of course keep your “06/05/26” looking exactly that - then it would be a text, not a date; and you would be unable to e.g. calculate date difference. Maybe that’s not needed for your case. Well - then just use the method already existing. But still - having something working not as you need, and trying to workaround it, instead of fixing it - is so poor solution.

[feedback]

@schiavinatto Thank you! Somehow it still flips dates… I input 05/12/26 and it just flips it to 12/05/26 - how would it even know what is a month and what is a day… It just flips them…

@mikekaganski autocorrect was disabled, so the only option is to make a proactive settings change as @schiavinatto suggested. Had I needed to operate dates with formulas, I’d indeed have to work with the date format, but this was not my case, I just needed a table to very simply write them down and not have it switch the dates after input… It would’ve been good had it issued a warning like “date recognized, swapped numbers of it to match the american format” with an option to disable this pop-up.

[EarnestAl]

If you are in USA make sure your locale is set to USA and you can set your documents to English (USA) in the screen shown by @schiavinatto

Note that you can always enter dates in ISO format, YYYY-MM-DD and they won’t change in a cell with the default General format

[feedback]

I now enter dates with dots. So a 30/06/26 is 30.06.26 so it won’t change 05/06/26 to 06/05/26. This looks horribly ugly, but unfortunately LibreOffice just can’t stop flipping my dates.

[EarnestAl]

It sounds as if you have chosen a locale that uses USA style dates. Consider changing the Locale to one that uses your preferred date format.

Your computer might not be set to the country in which you live. During installation, LibreOffice will take the location from that set in your computer. You can change to your actual location in LibreOffice in Tools > Options > Language and Locale > General

BTW if 30.06.26 is not changing like other dates then it is not recognised as a date but is just text.

[sokol92]

@feedback

This looks horribly ugly, but unfortunately LibreOffice just can’t stop flipping my dates.

I think together we can explain the current behavior of LO Calc in your instance and make the necessary adjustments.
To do this, open the attached file, press the Fill Indicators button, save the file, and upload it here.
Before running the macro, you can, if you wish, fill the cells in column A, starting with the first empty cell (A18), with any text that you have questions about when entering into Calc.

Survey.ods (11.9 KB)