#1

There are dozens of questions with this title and not one of them says how to actually disable date formatting. Please, if anyone knows how to just turn it off, please share.

I’ve disabled all auto-formatting that I can find under Tools->Auto Correct Options, but LibreOffice still tries to put a year on the text I just entered. There is no year. It’s not a fraction. I just need my text left alone without having to type extra text that doesn’t belong, like a leading space and zero.

2 Likes
Stop auto date?
#2

You don’t need to disable anything to do what you need. If you need some (all) cells to not try to analyze your input, but just accept it as text, then prior to input, you need to format the cells as text. You may create a cell style specially for that, or even modify Default style to have all cells have it automatically.

1 Like
#3

How do you “format the cells as text”?

1 Like
#4

Getting Started Guide | LibreOffice Documentation - Your documentation for LibreOffice, p.140 “Numbers as text”.

#5

Thank you. I would upvote, but this forum won’t let me. Just in case, is there some way to actually disable it? If I drag cells down and then type in the spaces between it reverts back to formatting them. I suspect that the truest answer to the original question is that you can’t.

#6

I mentioned that you should consider applying the formatting at style level. That gives you the possibility you need.

#14

I don’t remember which version of LibO first had the feature, but recent versions (and also already 5.4.4) have:

Go Tools>Options>Language Settings>Languages>Date acceptance patterns and remove all the patterns you don’t want to be used when trying to “recognize” input as a possible date. You may also enter a different pattern if you still want automatic recognition based on something uncommon. The only (implicitly preset) pattern you cannot delete is ISO-8601 delimited with 4-digit-year. (The month and the day also are acceptedcin 1-digit abbreviation). You also should not leave the respective input completely empty. If you did the settings would be replaced by the defaults again. You may put in something like D..M.. what will not actually occur. (Yes. It’s a mess.)

The funny replacements for some texts containing a slash like “1/2” by very special characters you need to remove via >Tools>AutoCorrect Options...>Replace from the list.

1 Like
#15

Note that this will not disable the date recognition completely - it will continue to work for special formats (I know that for ISO date format). Also that wouldn’t disable time recognition.

#16

I mentioned that concerning ISO extended. From my experience I don’t know additional patterns.

However: It’s a mess. We have a mixup of terms and concepts. Switching off recognition of formulas e.g. requires to set a ‘Text’ value under ‘Numbers’ format. … A clear concept is not in sight - and would probably be rejected by users claiming to know better. I personally think the recognition patterns for everything should completely be independent of format settings, and should be part of cell styles. The language field under Numbers also is more than doubtable - as generally the impact of locales. Concerning the input we even have a serious regression recently by introducing a mm:ss pattern depending on the format setting of the cell (Eike). This, of course, in pursuit of a feature request. …

#17

Thank you for your suggestion, but when I tried to use D…M… Libreoffice said that was an erroneous input. There seems to be very limited valid inputs for this field. Did you mean to literally enter “D…M…”?
Thanks

#18

I factually use “D.M…” (without the doublequotes, of course) in en-UK and in de locales as well sucessfully.
First time I also tried “D…M…” due to your comment in V 7.0.0.1RC and in V 6.2.5.2 portable with en-UK locale. It was accepted by both these versions and worked as expeced.
Did you actually remove all the other patterns (or use the semicolon as separtator at least)?
Always also regard the comments by @mikekaganski .

#19

I just wanted to chime in and ask why it can’t be a conceptual option to leeve this autocorrection behaviour as an option to the users, who know best what helps them to do their work?
I also don’t understand why it makes sense to choose the date format as the default? If there would have to be an unconfgurable default format, that user’s can not customise (why?), shouldn’t that be simple text at best, since it does the least harm and at least preserves the intended information in a directly readable format? I currently have a table with about 2000 lines, and right clicking each cell prior to enter text is downright a nightmare. Please developers, be open to user’s requests, in particular if this issue seems to bother user’s persistently and poses a real problem.
Making auto-correction behaviour fully configurable in general would help everybody and should just be a fundamental right :wink:

#7

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

  1. Highlight and right click the cells you want to leave unformatted.
  2. Click “Format Cells…”
  3. Click on the tab “Numbers”
  4. Under the category select box click “Text”
  5. 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.

Libreoffice calc disable auto date formatting
#8

This is totally incorrect interpretation.

Text cell formatting is specifically what you were asking for: disable any interpretation of entered text, and store it as-is.

If you need robust behavior in cases of additions/movements of cells, you need to apply the formatting to the whole column(s). As you seem to want to just disable the detection, (you don’t mention that you want it in some cells only) which might mean that you are using spreadsheet an a big text table, it might mean that it’s enough to modify Default cell style to have Text formatting. That’s all, and that gives you the spreadsheet without any input interpretation.

#20

This solution doesn’t work AT ALL.

#9

seems there is still no solution for the most annoying “feature”?
i would delete the format type “date” from my LibreOffice if i could. But even better would be the following: an option where i could deactivate the format whenever a cell format is applied automaticly.
since i work with text and numbers, and import both from external sources - and Libreoffice refuses to calculate with numbers in “text” format - i require the automatic format selection to be able to use the numbers.
I’ve lost enough hours finding and undoing date-formated input.
Note: “format the cells before” is NOT a helpful answer.

#10

Even in your case, you could format cells before: if you use spreadsheet as intended, i.e. as data organized into columns and rows. Then you know which columns or rows have numbers and need number detection, and which are for text.

If you don’t organize your data in rows / columns, then you are using the software not as designed. Of course, you are free to use it as you like; but then it’s not the software’s fault.

#11

Why did you (@Mark85) post this as an answer? It is none.

#12

Totally with you here. I’ve just wasted 1h battling with the stupid Date straight jacket. I have a column with data in dd/mm/yyyy hh:mm:00 and everytime I tell Libreoffice that it is TIME it changes it to DATE, which means that when I want to chart this I can only choose scale being AUTOMATIC/TEXT/DATE which means that it is impossible to meaningfully graph a 24h period of data, as it will be grouped by date, or it will have a hideous looking X-axis label inflation. I can’t understand why people down vote something without understanding what the respondent is saying.

#13

I don’t understand people trying to comment like that on what they don’t understand. The question here doesn’t tell about “dates” vs “times”. Of course, it’s difficult to read and comprehend strings like “I just need my text left alone” in the question, or multiple occurrences of word “text” in the answer we are commenting (reading is hard! :wink:); but the effort is worth it.

(A hint: your issue is not about formatting - and is unrelated to this topic. Yours is about date and time values and their chart plotting, and it needs a dedicated question, which would get different answers.)