How to disable auto-conversion of date permanently for every file in Calc?

English
, ,
#1

Every time there’s a document, I need select all and do format cell as text. Every file I open in Calc.

Any solution to this?

I just need an option to disable all auto-conversion permanently for all files. Let me turn it on if needed. A simple button like ‘Disable all auto-correction/conversion’ is all I need. I would prefer to disable all auto-correct but just date one is enough for now.

How it looks when I just type ‘01/06/2025’ starting from same files:
image

How it looks on Excel is in one of the comments I added( How to disable auto-conversion of date permanently for every file in Calc? - #3 by anutrix ).

#2

At first: No, it is no good idea.
If I send domebody a document wich calculates something it is usually wished to keep tje abilities to calculate and therefore needs my settings for cells as having date, number, percent-values, formula etc. (Otherwise I’d export as pdf.)
So don’t expect an option to treat everything as text.
.
For your new files, you can just save the prepared empty file after need select all and do format cell as text as a template. Then set this as your default template for new files.
.
This will be a default. It is still possible to use other templates. So no “permanently, for every file”. Keep this in mind, when using your OS to “create new Calc file”. Some time ago there were external templates used for this. They may differ from your default. If you need to change something there, depends on your unknown OS an Versions of LO. Also, ther may be other programs creating .ods or .xlsx files …

#3

I am not asking it to be default for others or all new installation.

Also, MS Excel works fine. It does not convert DDMMYYYY to DDMMYY . I don’t mind it being Number type but it auto-converts the format.

Additionally, you talk about sending this file to someone. The issue here is with document sent by person who literally stated it looks DDMMYYYY to him on excel and when he sent it.

Furthermore, this is not a feature removal request. The files are in my devices and I am just asking for freedom disable a feature that isn’t meant by author of file or me.
How it looks on Excel:
image

I added how it looks what LibreWrite Calc changes cell to in the original post. Excel does not make that change.

#5

When entering dates into cells, Excel and Calc work similarly (in Calc you can additionally specify which texts will be interpreted as dates, in Excel - no).
The default date format in Excel shows the year using 4 digits, in Calc the last two digits of the year are displayed.
One way to show 4 digits of the year in dates is to add the appropriate cell style and apply it.

#6

I think it depends on how the date was formatted in Excel and how the sending computer and the receiving computer are set up. The default is for the date to respond to changes in regional Date and time, see dialogue from Excel 2010
ExcelFormatDates

If you use ISO format YYYY-MM-DD then the date is unlikely to change as it is not asterisked in Excel and Calc always respects ISO format.

#7
  1. So is there a setting in Calc for it to detect the date format/locale?
  2. If not, is there a way to set it fixed format instead of locale based?
#8

Open the attached file.
On Sheet1, the first column has a style I called “Date4”.
My cells look like the image below.
In your copy of Calc, I think they are displayed according to your locale.
Try entering your dates in column A.
2025-09-05 142926

dateExample.ods (7.8 KB)

#10

image

It seems to show DD/MM/YYYY which is what I want.

Not sure but it seems DD/MM/YY is set by default on all cells despite changing Tools->AutoCorrectOptions-> Locale to correct one.

Let me see if I can changing default(LibreOffice Calc-wide not per file) locale and format for Date is possible and if that will work.

Maybe default

#11

The preset date format depends on the Language setting under cell formatting >Numbers. What’s yours?
BTW: Better dismiss DD/MM/YYYY., It’s ambiguous and can cause serious misunderstandings because the “standard” in USA is MM/DD/YY. Better avoid slashed date formats completely.
Use YYYY-MM-DD.

#14

TextEdit.ots (10.6 KB)
Make this template your default template and you get a tabular text editor.

#15

@anutrix your screen shot of sokol92 sample shows USA date format, MM/DD/YY. If you want to see DD/MM/YY you will need to set a locale that uses that date format. Click Tools > Options > Language and Locale and set your location. You could set English (Canada) and the sample will show
English(Canada)DefaultDateFormat

Either that, or always format date cells as YYYY-MM-DD which will not change in Excel nor Calc, not even for different regions like USA, UK, or Somalia (which uses both D/M/Y and M/D/Y)

#9

If this is actually what you want, it’s simple: Edit your template and therein the cell style Default. Change its >Number>>Format Code from General to Text.
However, you need to consider that cells formatted this way will also not recognize formulas.
At least you will need one or more additional cell styles based on a root style which is NOT set to Text.
BTW: The “correct” format for dates is YYYY-MM-DD. You should always use it in date cells. This can be forced (e.g.) by editing the numberformat of the style and setting its >>Language property to (again e.g.) English (Canada).
The setting will not interfere with your Date acceptance patterns.

#12

Thx. It seems setting locale in ‘Tools’ → ‘Options’ → ‘Language and Locales’ works. Only problem is it can set locales not actual default format.

Where’s the date format(not locale) setting for all documents?

I would be happy if everyone moved to YYYY-MM-DD but I am asking for users who want DD/MM/YYYY in all files(including xlsx) they open(not just create) in LibreOffice Calc.

#13

There is no such setting.
If you set UI lanuage and Locale, the Default cell style’s Numbers Language will be set respectivels (with the prefix Default - ). You can choose a dfifferent Locale (even the same without the Default - ).
This setting implies the automatic date format for the cells having set that style.
If you want to get DD/MM/YYYY (against my explicit and well considered advice) you need to know and set a language - (country) for which this is standard. Judged from my experience English (UK) should do.
Since I never use spell checking in Calc, I can’t tell if this interferes with tha setting.

#16

I

I don’t have experience with MS Excel, in specific not with the ~.~~~x files and how they behave when opened with LibO Calc. Have no MS Office for many years now, but I still dare assume that MS aren’t interested in quality and even less in compatibility, but in sales. They may therefore still not support cell styles.

Being interested in your problem anyway, I would need one (a small one) of the documents you want to work with, saved as .xlsx. Might you attach one, please to your reply?

#17

They do support non-hierarchical cell styles since 1993 at least. Nobody uses this most convenient feature. Excel users record VBA macros to generate hard formatted monster docs rather than using templates and styles.
As far as I know, Excel’s date/number/text input works in the same way as Calc’s.
You enter something, and if it is recognized as a date, the cell gets the right numeric value. If the number format is “General”, a default date format with 2 year digits is applied to the number.

#18

As far as I understand, it is the 2-digit year number in dates that does not suit the author of the topic.

#19

Nobody likes that, but that’s how it is with all Excel-ish spreadsheets. If you don’t want to format your date cells explicitly, you’ve got to enter everything as literal text. The template I’ve uploaded has number format @ (literal text) and language [None] in the Default cell style which suppresses all evaluation and auto-correction, turning the spreadsheet into a tabular text editor without spreadsheet functionality.

#20

I doubt it.
The Excel-ish spreadsheets I know (except Calc) display 4-digit years by default. For example, ONLYOFFICE.

#21

You are right. Gnumeric too. Gnumeric is inconsistent here. =TODAY() is displayed with 2-digit year, constants with 4 digits.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.