# How do you disable date formatting in LibreOffice Calc?

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.

edit retag close merge delete

Sort by » oldest newest most voted

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.

more

How do you "format the cells as text"?

( 2019-01-29 20:52:38 +0200 )edit

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.

( 2019-01-29 22:26:51 +0200 )edit

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

( 2019-01-29 22:33:32 +0200 )edit

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.

more

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.

( 2020-02-04 15:51:05 +0200 )edit

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. ...

( 2020-02-04 16:02:39 +0200 )edit

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

( 2020-07-11 20:31:03 +0200 )edit

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 @Mike Kaganski .

( 2020-07-11 21:10:00 +0200 )edit

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.

more

1

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.

( 2019-01-30 09:32:56 +0200 )edit

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.

more

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.

( 2020-02-04 14:56:14 +0200 )edit

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

( 2020-02-04 14:57:36 +0200 )edit

## Stats

Seen: 2,210 times

Last updated: Feb 04