Turn Off auto-date correction

[crystalkitten]

I need to turn this OFF. I've googled the problem, and I can't find the solution. I'm attempting to create my spreadsheet/csv for a paper, and for the initial, unedited data, I have a 2 part number (ie. 12/16). When ever these numbers CAN be converted to a date, OOcalc does so (but leaves them if they are not associated with a date). Ideally, I need to NOT change my input format. Not just for this project, but for others too, if a similar / or - format is needed for number input of measurements. Is there any way to turn OFF automatic date conversion? I've checked the autocorrect options.. and have tried checking the cell formatting stuff, but so far, no luck.

[gerard24]

Format the cell as text @ (before input).

[acknak]

Or, enter the values with a leading apostrophe: '12/16

The apostrophe is not stored with the text, it's just a sign that the entry should be stored as text.

[crystalkitten]

This is helpful, thank you. But I REALLY wish there was a blanket option to just disable this kind of conversion. Being in the sciences, it's more than a little inconvenient to have to manually change the format of a cell (which I tried to do for the whole column, but I couldn't figure it out), or have to remember to add the '. The ' is a good "work around", but really, this should be a setting you can disable for those of us who VERY frequently have to work with such data sets.

[acknak]

... I REALLY wish there was a blanket option to just disable this kind of conversion.

Follow gerard's suggestion: apply the cell format "Text" to the cells where you want to disable the automatic conversions.

A range of cells: drag the mouse over the cells to select; right-click > Format Cells > Numbers > Category: Text; OK

A column: click the column header to select the column; right-click > Format Cells ...

The whole sheet: click the rectangle in the upper left corner of the sheet (or Edit > Select All, or Ctrl+A); right-click > Format Cells ...

Every cell in the document: change the default cell style to cell format "Text"

Format > Styles & Formatting; right-click on style name "Default" > modify > Numbers > Category: Text; OK

Every cell in every new spreadsheet: change the default cell style to cell format "Text"; save the document as a template; set the template as the default template. See viewtopic.php?f=71&t=1161

Changing the cell format will prevent Calc from interpreting the entry but AutoCorrections will still occur. You can turn those off separately under Tools > AutoCorrect Options > Options > ...

[MrProgrammer]

The whole sheet: click the rectangle in the upper left corner of the sheet (or Edit > Select All, or Ctrl+A); right-click > Format Cells ...
Every cell in the document: change the default cell style to cell format "Text"
Format > Styles & Formatting; right-click on style name "Default" > modify > Numbers > Category: Text; OK
Every cell in every new spreadsheet: change the default cell style to cell format "Text"; save the document as a template; set the template as the default template. See v ... =71&t=1161

Changing the cell format will prevent Calc from interpreting the entry …

You should be aware that formatting entire sheets as Text, or setting the Default style to Text will prevent you from using any formulas in cells, because Calc won't interpret anything as a formula! An entry of =A1*2 will be accepted as five characters of text, not a multiplication formula. If you have only a few formulas, or only formulas in selected columns, you must set the cell style or the cell format back to General before entering the formula.

If you have no formulas, then the draconian action to set all cells to text format is perfect for you. But most people use spreadsheets so that they can perform calculations with formulas, and thus they have some cells in which they want automatic processing (formula recognition) to occur. I am not aware of any spreadsheet product which has a feature to automatically recognize formulas but nothing else.

It will help you to read section 5. Understanding data entry in Ten concepts that every Calc user should know.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Gesina11]

Hello
I'm new here, and have the same problem of the automatic date conversion, autocomplete something I try to put into a cell etc.
Several people sugested to format that cell into a text cell, nice, but I loose the posibility to calculate with that number, or get a proper sorting as other numbers are accepted.
What is happening is that there are a lot of lazy people who are being helped in being lazy, and the ones who like to put everything in by themselves are left standing in the cold.
If it is realy importand that these automatic functions do exist, make one of these little square boxes you can put a smal V into so that pepole who want it and those who want it not have a choice, and please do not make, for me, the most stupid machine ever made more clever than that it is.
I started working with them under C/PM, I forgot how many years ago, and they are stupid, the one advantitche they have above us humans, the are faster than that we are, so they can be helpfull.
I get always overexited when they want to make a computer more clever than that he is, but is it not simple just to make a switch to switch something like that on or of.
One day we will have realy smart computers, but I do not know if I will like them, not I'm the master over my computer, and the other way around I dont know if I would like that.

Kind Greetings and the best wishes for 2017

Rudolf
(Gesina11)

[RusselB]

@Gesina11/Rudolf: If you have the knowledge and time to put towards the task, the licensing agreement allows you to alter the base code, either directly or indirectly (aka an add=on/extension), to enable you to make the changes you are having trouble with.
As stated, there are ways of temporarily overriding the automatic conversion/autocomplete, but there are potential problems with setting these overrides as defaults.

[Villeroy]

@Gesina
Which concrete "number" are you trying to enter? If you want to enter a number and get a date instead, then what you entered is not a number anyway.

All spreadsheet programs allow all types of data in every single cell. As a retired programmer you understand that this simplification involves some evaluation process.
The most simple spreadsheet program is Calc because it knows only 2 data types: text and doubles. Functions may return error values as a third type but all constants you can enter/import/paste fall into the two categories text or number.
All dates are day numbers. "23/" is just a shortcut to enter the numeric value 42758 which is the number of this month's 23rd day. "23/7" is just a shortcut to enter the numeric value 42939 which is the number of this year's 23rd day in July. With US locale setting you would enter "7/23". The number will be displayed according to the formatting you have applied or - if you did not apply any formatting - according to some simple date standard corresponding to your locale setting.

All spreadsheet programs have leading character to initiate a formula expressions. This is the equal sign in most cases.
All spreadsheet programs have leading character to initiate literal text. This is the single quote in most cases. The leading quote is not part of the cell's text. '2017 enters the 4-character string "2017" into a spreadsheet cell not counting the leading apostrophe. Text formatting before data entry treats any input as literal text, including any leading = or single quote. In either way, text formatting or leading quote, you can enter numbers and formulas which won't be interpreted by any means.

If you want a slightly broken spreadsheet where it is really difficult to enter dates, then I recommend https://libreoffice.org/
There you have to explicitly define a format string for date recognition. The program is almost the same as OpenOffice. It produces the same document format. This program will tread your "number" as text. You do not even have to format anything nor enter the leading apostrophe.

[theYannis]

Hello and greetings to forum

besides the autocorrection problem to date, I encountered another issue.
when I edit a cell with a number without units, it automatically changes to percentage.
In my case the initial file was from MS excel 2000 and I saved it into open office calc file.

I have to manually re-change the cell number format. Once changed I think it stays that way, but it is kind of annoying having to do like 2-3 more clicks for every cell I edit.
Anyone else encountered this?

seems same kind of issue, so i didn't start new thread. I hope it is correct
Thank you

[Villeroy]

seems same kind of issue, so i didn't start new thread. I hope it is correct
Thank you

Date recognition is not an issue. It is wanted behaviour and more or less the same in all spreadsheet programs.
If you find it difficult to handle the formatting in your own documents, I'd recommend cell styles.

[jrkrideau]

Hello and greetings to forum

besides the autocorrection problem to date, I encountered another issue.
when I edit a cell with a number without units, it automatically changes to percentage.
In my case the initial file was from MS excel 2000 and I saved it into open office calc file.

No, I don't think it is the same thing. The autodate is not a feature, it is a spreadsheet "feature". It is just people who do not understand the peculiarities of spreadsheets that see it as an issue.

It looks like you are somehow importing a style definition from MS 2000. I'd follow Villeroy's suggestion: Create a new style and apply it to the cells.

[theYannis]

Thanks about the style tip.
But I think I figured it. There was a column with 10 different number formated cells. 3 of them were formated with %. When I tried to edit one of the rest of the cells in the same column Calc "thought" that I wanted that cell too to be % styled.
In other isolated cells, there was no problem.
I found out that I had to turn off auto-input and it was fixed.

As for the date recognition, the thing about dates is that you don't actually need them to be recognised because they are text/informative data and they are not used in equations. Also for the people that don't want the date recognition as a default feature, or a way to get it off....it is about 80% of times that the date recognition feature thinks that numbers that are not meant to be dates, are also dates.

[Villeroy]

Thanks about the style tip.
But I think I figured it. There was a column with 10 different number formated cells. 3 of them were formated with %. When I tried to edit one of the rest of the cells in the same column Calc "thought" that I wanted that cell too to be % styled. In other isolated cells, there was no problem.

That's what I thought. You can avoid this with the help of styles.

I found out that I had to turn off auto-input and it was fixed.

Auto-Input applies to text values. Number formatting applies to numbers.

As for the date recognition, the thing about dates is that you don't actually need them to be recognised because they are text/informative data and they are not used in equations.

Bullshit. Spreadsheet users want to sort their data by dates at least. They want to aggregate data per day, per month, per quarter, per year. They want to calculate interest rates, amortizations, due dates, birthdays and work hours. This forum is full of requests about date calculations. In most cases things don't work because of text values.

[Lupp]

Back to the original question of the thread:

In most versions of LibreOffice (starting with V3.6.2) you can delete any date acceptance patterns except Y-M-D. Unfortunately the feature is not implemented satisfactorily, and additional patterns may be accepted depending on the locale. However, if you choose 'Esperanto' for locale everything is fine. You get sound "recognition", and no silly replacements you don't want.

The one disadvantage for many users may be that 'Esperanto' only allows the comma as the decimal separator.

Even if you only set the cell language to 'Esperanto' you get the advantages regarding "recognition" (with the exception that M-D creates a date of the current year), and dates will automatically be displayed in ISO 8601 extended format (YYYY-MM-DD). Unwanted replacements need to be removed then, if not you switch off the replacement tool completely. The treatment of the parameter list separator is buggy, however, in a disgusting way if the original locale is using the comma for the purpose.

[Villeroy]

Yes, I have explained the slightly broken LibreOffice on January 2nd. But people are not interested in solutions. They want someone to utilize the software for them so they don't have to learn anything new.

[Lupp]

Sorry. I hadn't studied all the new posts to the old thread thoroughly enough. (Was skiing with grandsons for a wek.)

[theYannis]

As for the date recognition, the thing about dates is that you don't actually need them to be recognised because they are text/informative data and they are not used in equations.

Bullshit. Spreadsheet users want to sort their data by dates at least. They want to aggregate data per day, per month, per quarter, per year. They want to calculate interest rates, amortizations, due dates, birthdays and work hours. This forum is full of requests about date calculations. In most cases things don't work because of text values.

I am sorry if you thought that I offended you, but if you only see users in forums that the primary purpose of using numbers is dates, I have been in a dozen at least of other forums trying to keep my non-date numerical data turning to dates (that is about 95% of my usage for numbers).
Also there are a lot of people that actually use spreadsheets to make small calculation programs (I believe that is where "Calc" got its name), and those calculations actually DON'T work if the program decides to turn the values to dates.

Also it is way easier/less time consuming to sort the date by its numerical value if you input YYYY/MM/DD, than having to tell in all other cases that the other values actually are not dates.

PS I know I am new to this forum, but it is the first (of many) forum, that I got treated like this, for expressing an opinion that is actually shared by many users, that perhaps use the spreadsheets in a different way from yours.

[jrkrideau]

Thanks about the style tip.
it is about 80% of times that the date recognition feature thinks that numbers that are not meant to be dates, are also dates.

Not really. It is highly dependent upon who is using the spreadsheet and for what purpose. Lot's of people (see Villeroy's reply) want dates.

I think historically the audodate function makes sense since a 'spreadsheet' computer version was just intended to mimic as much as possible an accountant's paper spreadsheet where dates would be the desired default and we are stuck with it.

Lots of people with other needs than those putative accountants and Villeroy;s examples don't want dates and in fact the autodate "feature" can be catastrophic in some cases. See http://genomebiology.biomedcentral.com/ ... 016-1044-7 for some nasty examples.

To a great extent it just comes down to knowing how a spreadsheet works or, better still, not using a spreadsheet. My guess, from questions we see here and from other reading is that roughly 50--60% of spreadsheet use is inappropriate.

There are better and safer tools available but a spreadsheet looks deceptively simple to use and so people end up using them for all sorts of things they should not use them for.

People use them when a database is the only sensible tool; they use them for data manipulation when something like Python or Perl would be better; they use them for statistical analysis when something like SAS or the open source R would be the correct tools,; they use them for data entry when a database form or something like Epidata should be considered, and so on.

[soby]

hi to all
i have read all the comments and solutions and i have learn something
i will study how to setup database
but i have maybe a solution for the date not to change as date format
i have test these 20 01 2017 (with space) and 20.01.2017 (with the point form the numeric keyboard)
they are numbers when viewing the format
or maybe you can use \ to prevent the change in date format (12/16 ==> 12\16)
so no need to change anything for the spreadsheet

[Villeroy]

Also it is way easier/less time consuming to sort the date by its numerical value if you input YYYY/MM/DD, than having to tell in all other cases that the other values actually are not dates.

Typing 10 characters in a distinct format is time consuming and error prone. And you still won't be able to calculate with strings.

A "1/2/2010 " date string evaluates to 2nd of January in the USA and 1st of February in the rest of the world. "31/1/2010" evaluates to 31st of January or gives an error value in US context. Date strings other than ISO dates are ambiguous. True cell values are unambiguous.

One input method that works on the num pad of a PC keyboard:
1/ --> this month's 1st
1/2 --> this year's 1st of February (or 2nd of January in US context)
1/2/3 --> 2003/02/01

You can display the resulting cell value in thousands of different ways with weekday names, full month names, abrev. month names in 100 languages, with week number and quartal number because all that information can be derived from the numeric cell value. No matter how you display the value, you can perform financial calculations, statistics, charts with a true time scale, pivot tables grouped by months, quarters, years,...

This is wanted and expected behaviour in all types of Excel-like spreadsheets. And all of these programs treat a leading apostrophe as text mark and all of them allow you to declare text cells for literal input.

[theYannis]

I glimpsed Epidata for a while and I tend to disagree that it is can cover my needs easier than excel or calc or other spreadsheet.
I also noticed that you either have to type the whole date or those programs have a chance to "guess" the wrong date. I tried a few before I write this with calc and it made 3 correct dates and 2 mistakes out of five tries.
Time concerning this makes us compare the time of striking "-" twice with the time of "right click-->format cell" before we need to enter something that "looks" like a date but it isn't.

Many users use spreadsheets, because they combine equation tools, and allow them to make a small calculation programs, without having to know programming languages, such as the ones mentioned. Also it is a lot easier to just import images in your spreadsheet, than call them with the language commands.

Another convenience is that you are allowed to drag down to a column a bunch of calculations.

The frustration begins when the Artificial Intelligence of the programs "knows" that every time you type 10/12 or 10.12 or 10-12 the chance that it is a date is 100% and there is no other way to persuade it for anything else. Adding ' or other markers makes the presentation poorer and pressing "spacebar" before the entry has no effect.
There are times that a user wants to make a note or a title to mean version, fraction, range, etc and actually have the note stay like this, effortless.

Word processors are actually "smarter" than that and "ask you" whether to make a date by selecting the change and allow you to "Esc" or "undo" your way out of this.

As for the error and the importance of the dates to be correct, I fail to see how dates are the only important value to be automated and how those programs will know which date you need (for example, if you strike 14/12 by error, how will the program give you 17/12/2017?)
the rest of the input data aren't so important to be automated as well?

I am not that old to tell, but automate stuff and have begun to get introduced the last 10-15 years and the internet is full of users trying to find solutions to make the programs they used to work before, not to make decisions for them, especially when those decisions are actually wrong.

I will end by saying that I tend to agree that it is a useful feature, but I am also positive that it is less useful from the option to disable it.
Imagine making the cars turning left or right only 90 degrees just because the majority of the turns are like this and at some point you need to go around a stopped car. Unless you hit the break you will go through a wall. Everyone would go to a previous car model, even if it didn't have A/C.

[Villeroy]

I glimpsed Epidata for a while and I tend to disagree that it is can cover my needs easier than excel or calc or other spreadsheet.

Epidata is a database. Indeed, most Calc users want to use a database but can't.
In a database, every single value is strictly typed and all values are stored in fields of equal types.
A spreadsheet has no tables, no fields and only 2 or 3 types. Every single cell can freely take any type or a formula expression. Therefore some convention about the interpretation of input patterns is inevitable. This convention is more or less the same in all spreadsheets. Equal signs and apostrophes mark formulas and literal text. Everything else is interpreted. Cells that have been prepared as text cells only take literal text.
If these trivial rules are too frustrating, you can not use any spreadsheets.

[theYannis]

The thing with spreadsheet users is that those 10-15 years ago, spreadsheets had wider fuctionality. Those AI automations were missing and everyone could work normally. Now the developers made a "turn" and now they target a tighter group of users.
Unfortuantely for the rest of us, nobody had told us not to begin using those spreadsheets because "in the future we are going to mess them for some of you."

AI automations are useful for the new users or the users that sigle-purposely using something. The drawback is that they actually "kill" versatility of the programs for the users that knew or could imagine how to make more use of a program or tailor it to their needs.

It is a undeniable fact that they initially created a need and then killed the supply. I can't think of another reason why so many users suddenly (arround 2006-2007) begun to find work-arrounds and solutions to automated stuff, all over the web.

[jrkrideau]

Yes. Unfortunately designers insist on improving things.

[Villeroy]

The thing with spreadsheet users is that those 10-15 years ago, spreadsheets had wider fuctionality. Those AI automations were missing and everyone could work normally.

In this topic we discussed something that always worked in the same ways since the 80ies. I remember Excel5 (~1993) and Lotus 1-2-3 for MS DOS (1988). Dates were numeric sub-types and when you entered them as text, you had to face certain draw backs. Now I feel sorry that I dumped my old Excel book. Data types and data entry was chapter 2 and 3 before formulas and references. And I remember that we had to type 2 apostrophes in order to start a text with 1 apostrophe due to the simple rules that were the same as in todays Calc.

What YOU have in mind is an artificial intelligence constantly reading your mind about your intentions when entering 1/2 into a cell. However, such AI does not exist. Instead we have the same set of simple rules since decades.
Text or number (or boolean in Excel)
Leading = for formulas
Everything will be evaluated as a number if possible (this is arithmetic software)
To suppress formula evaluation or numeric evaluation you have 2 choices: text format or leading apostrophe.

Original documentation of Visicalc(1979): http://www.danbricklin.com/history/refcard1.htm
Setting a label entry
Label entries start with a letter A-Z or with the quote character (") [...]
Setting a value entry
[with an explanation of possible numeric entries. no dates supported at that early time]


AND AGAIN: Today we have a slighly broken spreadsheet application called "LibreOffice Calc". This one is dumbed down to meet the requirements of todays users who can not understand simple rules of generic software. Date entry is mostly disabled. You wll love it.

[Lupp]

May I add my two cents?

Developers surely should improve things. They should focus, however, on specified functionality not yet implemented correctly to the full extent. There is a lot to do under this maxim. The next intention should be the improvement of efficiency - and everything under the objective of keeping and enhancing a clear structure.

Factually much of all "improvement" is aiming at the huge field of adipositas pseudofacturis often going along with severe featuritis. To be clear: It's not about curing the disease but about enhancing its funny effects by conspirative iatrogenic action. (You see, I sometimes like to mimic a satirical comedian in a distorted way.)

Let's look at the other twin, LibreOffice, presently developed much more lively than AOO. The next version 5.3.0 (talking of RC1 here) comes with 252 locales (I actually took on to count them). Any new version m.x has a "feature freeze". Any release m.x.y has a "hard code freeze", but allows for "important bug fixes, and l10n improvements" from RC m.x.y.z to the next RC. (The silly "l10n" standing for "localisation".) Thus there is hope that we have more than 300 l11s before 2017 is out.

None of the new features is specified clearly for the public - and mostly not even for developers working on them, I'm afraid. Localisations are most likely not thoroughly checked. Shouldn't we at least oblige developers not strictly bound by a preset specification to specify (and grant access to the specification) themselves what they implement instead of insisting on "as is" as a surrogate? "As is" will never allow a refutation. It thus is mortal to "must be correct".

You may also know that LibO has an editable property on the application level called "date acceptance patterns". Why isn't it taken as the base for deleting all the silly "default acceptance patterns" (now: DAP) per locale and leaving this subjct to the user? Why isn't it completed by an option 'use first DAP as default for automatic formatting'.

Finally a few inexorable truths (which you may mistake for theses):
1) If 12/1/3 is "accepted as a date by pattern" and evaluated to the equivalent of 2003-12-01, 13/1/3 must be rejected as an error instead of treating it as text without notice.
2) If a date or datetime is entered in ISO 8601 format it must not be displayed in any silly locale format.
3) Displaying dates with less than 4 digits for the year must be judged a criminal offence. Saving dates as texts with 2-digit-year or specifically in MM-DD-YY must incur the maximum penalty.
4) It is urgently necessary that a few locales in the sense of "antilocales" be created, usable to switch off most of 'Recognition', 'AutoCorrection', and 'Completion' at once.
5) Users must be allowed and expected to consciously declare what they mean with the few exceptions they themselves defined or expressly accepted.
6) Any locale needs to cooperate with an additional property allowing to use TheOtherDecimalSeparator as compared with the one preferred by the locale.
7) The only GroupSeparator accepted (if any) must be the small space, probably replaced by a full space where fixed width is needed. That neither the comma nor the point shall be used for the purpose is expressly stated by ISO 31-0.
8) We should generally regard international standards. Where everyday usabilty urgently requires some deviation the mildest one is appropriate.
9) Formatting in conflict with the above principles may be inavoidable when quoting or demonstrating historical styles, e.g. In these cases explicit marking should be used.

[Villeroy]

Why that complicated? Apart from the 2-digit years, AOO Calc, Excel and Gnumeric do it right and LO does it wrong for me particularly because they implemented some of your points. Nevertheless I use LO for non-technical reasons and I can use it without too much effort because I do understand how its dumbed down entry mechanism works and I am still young enough to adjust myself.
The problem is not how things are designed. The problem is users that do not want to use and developers implementing personal preferences of the former.

[Villeroy]

1) If 12/1/3 is "accepted as a date by pattern" and evaluated to the equivalent of 2003-12-01, 13/1/3 must be rejected as an error instead of treating it as text without notice.

The same kind of users who started this topic and similar topics all over the internet will instantly remove the software. Databases are strictly typed. They do not allow input of any types that do not match the type of the input field and you can bind specialized form controls for dates, times, currencies, check boxes for booleans etc. But most people who use databases every day when they go online shopping, are completely unable to build up a most simple database for their own purposes.

2) If a date or datetime is entered in ISO 8601 format it must not be displayed in any silly locale format.

LO does that. But now you have to explain why it does auto-formatting with ISO values. Why not doing the same with any date format you throw at it? Because that would require artificial intelligence. You enter something. The something is recognized as a date and in addition it generates and applies the matching number format for your input. That would be the type of technical overkill everybody complains about but which is not built into AOO. Now it recognizes date input and applies one distinct date format unless you did not specify any.

3) Displaying dates with less than 4 digits for the year must be judged a criminal offence.

+1 but at the time when this became a pseudo-standard, the 2-digits were sufficient because nobody calculated with dates in the 21st century and Excel never supported dates before 1/1/1900

5) Users must be allowed and expected to consciously declare what they mean with the few exceptions they themselves defined or expressly accepted.

Text or number (or booleans in Excel) or formula input. Easy as that. If such a feature would be added to a spreadsheet program, nobody would use it, because it would require extra work before input. And again: All this is fully implemented in database software. People do not accept to prepare text formatting before input. Why should they accept date formatting before input? In the 90ies dozends of spreadsheet alternatives came up. First question was: Is it compatible to Excel? No? Then I won't even download the thing for free. How would your software handle existing sheets with mixed types?

6) Any locale needs to cooperate with an additional property allowing to use TheOtherDecimalSeparator as compared with the one preferred by the locale.

Way easier: all input is interpreted in the context of the global locale, just like input of constant decimals in formula expressions (=1.23 vs =1,23). If your locale is English, then every point within digits should be interpreted as a decimal point while you are free to apply a European number format with decimal comma. Right now, we have to change our input method with the localization of individual cells which is a pain in the ass when working with international documents (e.g. tutorials and documentation). But guess what will happen: people will not understand why input differs from display.

7) The only GroupSeparator accepted (if any) must be the small space, probably replaced by a full space where fixed width is needed. That neither the comma nor the point shall be used for the purpose is expressly stated by ISO 31-0.

Group separators, also refered to as "thousands separators" are not a serious problem. For professionally looking documents you need them in either locale dependent variant. Nobody uses them for keyboard input.

8) We should generally regard international standards. Where everyday usabilty urgently requires some deviation the mildest one is appropriate.

AOO correctly interpretes ISO dates and ISO times in cells and form controls. Sheet formulas can even calculate ISO date strings, for instance ="2000-02-01"+1 whereas ="1/2/2000"+1 fails for good reasons. But the same non-users who complain here would never accept any international standard. They only accept what they have in mind right now.

9) Formatting in conflict with the above principles may be inavoidable when quoting or demonstrating historical styles, e.g. In these cases explicit marking should be used.

Non-users spend all their time with formatting until the point where wrong data look right and correct data look wrong. Only values are of importance. But the non-users can not distinguish between formatting and value like they can not tell text from number.

[theYannis]

Non-users spend all their time with formatting until the point where wrong data look right and correct data look wrong. Only values are of importance. But the non-users can not distinguish between formatting and value like they can not tell text from number.

This is offensive and couldn't be more wrong.
E.g.
input 12-10, Calc gives 12/10/2017, change format to text and it is not returned to 12-10, but 43020. This means that Calc actually changes the data you input. If the data is 12-10, the so called "non-users" spend their time to make it look like 12-10. The actual value is 12-10. If there are actually 4-5 different values that could be generated from the 12-10 and AI always asumes one of them, then AI is badly coded.
This is actually a dictionary-like procedure, especially if the program doesn't recognise 13-13 (what happened there?), where even the cheapest smartphones actually give you the option ot choose from the proposed result and your initial input.

I see that this conversation leans towards: your need VS my need and not the fact that the programs could cover both needs. It doesn't even bother to make the choise of value as an undoable action for the user to press "enter" to keep it or "Esc" it or "undo" it with ctrl+Z.

I think this is far beyond out of topic. the actual answer to the initial question is: this ability/feature is no longer available and you should use the work-arounds all over the web.

PS I am sorry about the rage but "your need VS my need" for what the program should/could do is the worst argument I have ever seen.