Blog

It’s a really annoying ‘feature’.

I’m searching for a way to disable the very annoying number formatting in Libre Office Calc. Whenever I enter some number or a string containing numbers, LO is trying to format a date out of it.

Pseudo-solutions:

I’ve found some so-called-solutions, but none of these works.

• Format cells to “text” – works, but only as long as one pastes or deletes from/to a specific cell. For example, if I paste some content, the formatting is lost again.
• Start typing with a single quote '. Not an option in daily working routine, just to enter a numeric string.
• Deselect Tools > AutoCorrect > Options > Apply Numbering – there is no such option in Libre Office (at least not in version 3.5).

Use cases:

• 2-3 means “two to three whatever”
• 5.2 is the code following 5.1
• 6. refers to the sixth item

All those values are translated to some random date in Libre Office by default. I guess they were on drugs when implementing such a bug into the program.

Is there a global setting to turn off that featurebug?

There are two main workarounds: 1) manually adding ‘ in front of numbers, which will force treatment of them as a character string. 2) setting the format to “text” before entering text.

Neither of these are good solutions for everyday work. For instance, if you paste in data from somewhere else, it will not generally have ‘ in front, and it will also override the format you chose. A last trick here is to use “paste special” and then choose the types, which can be a good workaround too.

It’s not a new complaint:

• May 19 ’12, http://ask.libreoffice.org/en/question/2756/disable-date-formatting-in-calc/
• Sep 6 ’13 http://ask.libreoffice.org/en/question/22273/date-recognition-in-libreoffice-calc/
• Jan 8 ’13 http://ask.libreoffice.org/en/question/9705/calc-how-to-disable-number-formatting/
• Feb 29 ’12 http://ask.libreoffice.org/en/question/672/how-to-deactivate-automatic-date-formatting-in-calc/
• Thu Apr 23, 2009 https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=17938
• and hundreds of more on Google

Developers don’t seem to understand the users’ frustration. Instead they write stuff like this:

At first I thought you meant you must handle cell formatting for each cell individually, which of course is false. (You select all the cells and choose “text” as a data type.)
Now if I understand correctly, you want a way to disable automatic date recognition globally, for all spreadsheets? Or at least, you want to know why that is not in the preferences section?
I can at least make a guess at the last question. It is probably for the same reason why there’s not an option to globally turn off automatic recognition of formulas. Because that’s what Calc is for….
In the vast, vast majority of cases, a user will expect that if he types in a date, it will be “understood” by his software as a date. If it doesn’t get “recognized”, he’s going to think “Hmmm, this software isn’t very good.” He’s not going to think, “Hmmm, there must be a global setting somewhere that’s been switched off so that dates don’t get recognized,” and then go hunting for that setting. (If that did happen and he actually found the setting, his inevitable question would be, “Why on earth is that even an option? Who would want to turn off the date recognition for all spreadsheets?” And we’d have to tell him, “Well, it was this guy Swingletree….” ;)

This is a typical example of developers being out of contact with normal users. For normal users (>95% of users), this auto-conversion is more of a bug than a feature, which is why people want to turn it off completely, and then just manually tell Calc when to interpret something as a date.