Ask Your Question

How do you disable date formatting in LibreOffice Calc?

asked 2019-01-29 20:03:55 +0200

Cruzy gravatar image

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 flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted

answered 2019-01-29 20:15:12 +0200

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.

edit flag offensive delete link more


How do you "format the cells as text"?

Cruzy gravatar imageCruzy ( 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.

Cruzy gravatar imageCruzy ( 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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-29 22:33:32 +0200 )edit

answered 2020-02-04 15:12:54 +0200

Lupp gravatar image

updated 2020-02-04 15:51:20 +0200

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.

edit flag offensive delete link 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.

Mike Kaganski gravatar imageMike Kaganski ( 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. ...

Lupp gravatar imageLupp ( 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

Maxfactor gravatar imageMaxfactor ( 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 and in V 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 .

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

answered 2019-01-30 01:33:41 +0200

Cruzy gravatar image

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.

edit flag offensive delete link more



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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-30 09:32:56 +0200 )edit

answered 2020-02-04 14:14:27 +0200

Mark85 gravatar image

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.

edit flag offensive delete link 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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-02-04 14:56:14 +0200 )edit

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

Lupp gravatar imageLupp ( 2020-02-04 14:57:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-29 20:03:55 +0200

Seen: 2,210 times

Last updated: Feb 04