[Issue] No "Off" Option for Automatic Date Changes

[Hagar Delest]

I use OpenOffice 3.0.0, and I was going into calc to arrange some numbers on the screen to better visualize a project I'm doing, but I found that entering "1/14" caused the contents of my cell to be replaced by a date. So I went into the help and disabled every automatic formatting tool I could find since I don't like those anyway, but it still wouldn't stop replacing what I put in it. So I finally find the help page specifically on that issue, and it says the only thing I can do to keep it from replacing what I type in is to put an apostrophe before every single cell. I find it flat-out absurd that a feature supposedly put in for convenience has no off button, and requires the user to inconvenience themselves just to get around it. Please, please, never put in any automatic feature that doesn't have an off option, and better yet, add a master off switch for all the automatic features.

[Villeroy]

The issue has nothing to do with formatting. What is it what you want to enter actually? Certainly, you don't want 14th of January. Do you want a literal string "1/14" like "one of fourteen"? Or do you mean a number one fourteenth (0.07143)?
The spreadsheet is designed to never reject any of your input nor does it interfere by any means. It uses some simple assumtions when it tries to guess what you intent to enter. You can override this at any time for each single cell or many cells at once.

[ziv]

I want it to display the string "1/14". If there is a way to do this without putting an apostrophe before every cell I want to do this in, then it should be listed in the help under "AutoInput function on/off" which it currently is not.

[Villeroy]

Number format "Text" (no number at all) takes all input as literal string.

[Falkewave]

It uses some simple assumtions when it tries to guess what you intent to enter. You can override this at any time for each single cell or many cells at once.

Is there a way to override this "feature" when opening a file that already contains strings that Calc will assume are dates? As of now, Calc is automatically changing these strings as soon as the file is loaded. Changing the format to "Text" does nothing in this case.

The only solution I have is to change the "Date" format by getting rid of leading zeros and the year, but that isn't practical. Is it possible to change the defaults or how Calc interprets these strings when opening a spreadsheet?

[Villeroy]

Is there a way to override this "feature" when opening a file that already contains strings that Calc will assume are dates?

A spreadsheet cell has either text or a formatted number. Unlike Excel, Calc regards the difference strictly. It will never convert text to number or vice versa unless you tell it to do so.

As of now, Calc is automatically changing these strings as soon as the file is loaded.

If the word "file" refers to a plain text-file, some flavour of csv or something, you should honestly tell us about this important matter of fact. In this case I guess that the respective field with values like "12-03" is not quoted as suggested in several conventions on using plain text tables (csv). In this case your problem is that Calc makes no assumptions about your data being text or not. It just treats 12-03 as if you enter 12-03 into the respective spreadsheet cell. Anything else would be auto-magic behind the scenes.

Changing the format to "Text" does nothing in this case.

No formatting will never change already existing values nor will it convert water into wine or vice versa. This is a very important feature, indeed.
My statement ...

Number format "Text" (no number at all) takes all input as literal string.

... implies that you can prepare the cells before entering any values.

If you are working with plain text files or text-tables in the clipboard, you can specify how to interprete each field's input values. Just pick a column in the import dialog and change it from "Standard" to "Text", "US-English", "Date(YMD)", "Date(MDY)", "Date(DMY)".
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=13587.

Unfortunately, there is only one single feature where Calc does not give you the control: menu:Insert>External Data... where you can link a range to a html-table. None of the above methods prevents 12-03 to be imported as numeric "12th of March" or "Dec the 3rd" respectively, depending on your application locale (US style or not). It overwrites all previously existing formatting, trying to apply some of the html-tags.

A database application does not have this problem since it is absolutely clear what 12-03 is supposed to be, depending if you put it into a text field or not. If in doubt, it will simply reject the input as invalid for the respective field.
A spreadsheet has no such fields. It is intentionally designed as freehand-tool, never rejecting any input, happily mixing numbers and text with formulas, which itself implies that each single cell must accept (and interprete one way or the other) whatever you throw at it.

[Falkewave]

If you are working with plain text files or text-tables in the clipboard, you can specify how to interprete each field's input values. Just pick a column in the import dialog and change it from "Standard" to "Text", "US-English", "Date(YMD)", "Date(MDY)", "Date(DMY)".

This is exactly what I need to know.

Thanks for the clarifications and for putting up with the ambiguities in my post.

[hughesjr]

Unfortunately, there is only one single feature where Calc does not give you the control: menu:Insert>External Data... where you can link a range to a html-table. None of the above methods prevents 12-03 to be imported as numeric "12th of March" or "Dec the 3rd" respectively, depending on your application locale (US style or not). It overwrites all previously existing formatting, trying to apply some of the html-tags.

Is there a way to fix this issue. I am pulling down a specific set of information from an html table and the column contains things like 12-3 or 0-1, etc. Maybe some way to create a custom filter or something?

I really need to control the format for linking to the external data and I have no way to control the web page where the input comes from.

[Villeroy]

Calc fails to import html with table data like "1-2". There is no work-around, no fix, no nothing. You can not control how html-links are imported through menu:Insert>Link From File...

Edit: OOo 3.3 fixed the html issue. You can turn off any detection of "special numbers" such as dates

[acknak]

It seems that the general issue was addressed for OOo 2 in Issue 50670: HTML Import and Clipboard Paste behaviour, where it was decided that the data should be imported according to Calc's locale setting, using the same rules covering data entered by the user.

I suppose that was an improvement over bumbled guessing of the locale of the html document, but it's certainly no solution: Calc can no more guess correctly what the value type should be for a particular column or cell than it could guess for the whole document. There has to be some way for the user to specify the column types, just as when importing a text file.

There is no request for that specific feature; this is the closest I found: Issue 71997: Html online import of numbers and dates is very inflexible, actual preference description is wrong

You can register there and file a new request (please add a link here if you do that), or add your vote (up to two) or comment to an existing issue.

[hughesjr]

I added some info about this in Issue 71997.

[gcb]

1. write "7.1.1" on a cell
2. enter
3. feel idiotic as it changes to 07/01/2001
4. change formating to General or text
5. feel even more idiotic as it changes to 36898

[Villeroy]

Format as text. This is the switch to turn off all evaluation of the selected cell(s). Then enter your strings into the prepared text cells.
WIthout this preparation you can enter your strings with a single quote ('1234) The quote serves as a tag. It won't be part of the text value.
You are the one who imports or enters cell values. No formatting attribute, no colour, no border, no font, no language setting, no number format will ever change a single existing value. Formatting a formatted number as text will never change the number, the formula nor convert the number or formula to string. It shows the cell's numeric value as unformatted decimal until you enter something else which then will be taken as literal text.

[autoformatsufferer]

I suffer from this every time I use a spreadsheet. gcb's example is easily reproduced and very annoying and clearly does change the value. So many people complain about this, why don't you just add an option to disable any autoformatting at all. Even if everything I enter is assumed to be text, unless I explicitly format it as something else, it would be better than the constant wrong guesses.

[TheGurkha]

So many people complain about this, why don't you just add an option to disable any autoformatting at all.

We're not the developers, just users trying to help other users. The developers don't hang out here. To report bugs or make suggestions, see this tutorial: [Tutorial] Reporting bugs or suggestions

[jrkrideau]

I suffer from this every time I use a spreadsheet. gcb's example is easily reproduced and very annoying and clearly does change the value. So many people complain about this, why don't you just add an option to disable any autoformatting at all. Even if everything I enter is assumed to be text, unless I explicitly format it as something else, it would be better than the constant wrong guesses.

As Villroy says "Format as text."

You need to format the cells before you enter any data.

[irritated_user_89]

This IS actually a form of Autocorrect, a god-damn annoying one, yet when you turn off whatever you can turn off in Autocorrect, this still is there, bugging the hell out of you.

I tried to type "7-11". As in "varies from 7 to 11." And Calc kept changing it into a date. I turned off Autocorrect, yet it still was there. The solution is, as found here, to format every cell you are going to use into "text". This should not be the case and is so annoying I could kill a kitten.

My blood pressure was almost through the roof. Don't say Autocorrect is turned off when it is NOT.

I'm posting on both topics I could find, Googling for this terrible error/bug. It is so annoying and everyone should complain about it so that it gets attention and gets fixed.

[mriosv]

+7-11 enter as value not a date.
+7/7/11 also enter as value.
=7-11 enter as value not a date.
=7/7/11 also enter as value.

[Villeroy]

This IS actually a form of Autocorrect, a god-damn annoying one,

No, it's no form of correction because you get exactly the value you entered in the format you applied. In case you refuse to apply number formatting, Calc applies one particular format according to the locale and the "numeric flavour" you typed (one particular format for date, time, percent, currency, ...)
If numeric input "2010/12/31" would be formatted exactly as entered, that would be auto-formatting indeed. You could not tell anymore if you just entered a number or a literal string. A consistent look would be more difficult.

[JustPassingThru]

Mr. Villeroy,
I wish to have the following text, "+Man" (without the quotes) appear in a cell.
Calc is bound and determined to "fix" it for me, no matter how I type it in.
The "+Man" is always replaced with something else on the page, and I cannot override it.
Excel would at least "eventually" let me enter it the way *I* want it, if I preface my entry with a single apostrophe.
If I try it in Calc with a single apostrophe ('), sometimes I get the text displayed with a single apostrophe before it, the rest it replaces the text "for" me.
I must admit my single biggest frustration with Calc, is that I cannot override some of these "helpful" behaviors.
I have disabled every auto* I can find, because I find them less than useful. I am always fighting them.
But some (like this) cannot be turned off.

You are arguing that we are getting what we type in.
But you are missing our points, that what we type is in misinterpreted by Calc.
"7-11" means "seven through eleven" not "July 11th" or "November 7th" in some contexts.
Calc is getting it wrong.

How can I tell Calc to stop "helping" me, and just take what I type as "text and text only, I mean it."
for one cell only, or for a range of cells, or for one sheet, or forever (or until I change it).

I think you will satisfy many of us, with that little tip.

Thanks.

[Villeroy]

JustPassingThru
I referred to numbers where the same values can have many different representations. The simple answer for Calc and all the other spreadsheet applications is: Format your numbers or accept the defaults. If it is 7th July, 7/7 or anything else is unimportant as long as the cell has the right value so the subsequent formulas do the right thing.
You seem to have a problem with text. Turn off the auto-correct features and/or turn off the auto-complete feature.

The one and only problem with all these questions in any spreadsheet forum (including Excel forums) is that nowadays any level of spreadsheet expertise is rejected as being way too geeky.
Nobody reads books, nobody even tries to understand the clear distinctions between values and formats, numbers and text, absolute and relative references.

[TheGurkha]

How can I tell Calc to stop "helping" me, and just take what I type as "text and text only, I mean it." for one cell only, or for a range of cells, or for one sheet, or forever (or until I change it).

As was stated above, format the cells as text before you type into them:

Highlight the cells (or the entire sheet if you want) and go to Format > Cells > Numbers Tab > select Text from the Category drop down menu.

[Thoskk]

All those solutions are very inconvenient if you try to work efficiently and quick.
I do not want to pre-format every single cell in a row of 100 or more to suit the input I don't even know before.
It would be much more convenient to firstly take what the user puts in 'as is', and maybe then let him choose from some kind of dropdown menu or similar (as in excel), if he want's to keep the format or change it, and if always to do so or not.
Even worse is the fact that when I type something like 24/7 in German language setup, Calc changes it to a date format anyway - although in German language the date is written 24.7. ! That really should be changed.
I don't like MS Office programs for automatically changing many things and calling the user a stupid nerd for it. But at least, I can switch off ANY automatism in MS Office programs. Why not in OO?

[jrkrideau]

All those solutions are very inconvenient if you try to work efficiently and quick.
I do not want to pre-format every single cell in a row of 100 or more to suit the input I don't even know before.
It would be much more convenient to firstly take what the user puts in 'as is', and maybe then let him choose from some kind of dropdown menu or similar (as in excel), if he want's to keep the format or change it, and if always to do so or not.
Even worse is the fact that when I type something like 24/7 in German language setup, Calc changes it to a date format anyway - although in German language the date is written 24.7. ! That really should be changed.
I don't like MS Office programs for automatically changing many things and calling the user a stupid nerd for it. But at least, I can switch off ANY automatism in MS Office programs. Why not in OO?

You would have to ask the developers not us. We're just users.

A partial workaround is to set up styles and create a new template. Apply the style before entering the data. This is probably the most efficient way to work since is effectively prevents some kinds of nasty errors.

Personally I always worry about someone multiplying by a telephone number in Excel or Calc.

[frankol]

How can I tell Calc to stop "helping" me, and just take what I type as "text and text only, I mean it." for one cell only, or for a range of cells, or for one sheet, or forever (or until I change it).

As was stated above, format the cells as text before you type into them:

Highlight the cells (or the entire sheet if you want) and go to Format > Cells > Numbers Tab > select Text from the Category drop down menu.

This is bullshit. Even if you format a cell as text, it still autocorrects dates for me. So far I have found no way to stop it at all.
My specific problem is that it remove capitalization for weekdays. It is incredibly annoying. I do not want Calc to autocorrect anything at all.

[acknak]

You are aware that people here are volunteering their time to try and help, right?

I know it doesn't really matter, but the advice is accurate for preventing Calc from interpreting dates, but this is not Calc fiddling with your dates. What you're seeing is Calc trying to help you with your typing.

Go to Tools > Options > OOo Calc > Sort Lists and delete any of the lists there that may interfere with your work.

You have to delete them (or modify them to not match the actual names)--there's no way to simply disable them, as far as I know.

Edit: Oops--sorry. My bad. You can disable the sort lists, as well as copying from previous cells, with Tools > Cell Contents > AutoInput: NO (unchecked).

PS: There is an issue asking for this to be made easier: Issue 110949: "Format – AutoCorrect – While Tpying" not available in calc
You can register there and add your vote (up to two) or comment.

[teejangry]

I see the same behavior in OO Writer inside tables while autocorrect is turned off. This is an extremely irritating "feature" to have inside a text document.

[Villeroy]

I see the same behavior in OO Writer inside tables while autocorrect is turned off. This is an extremely irritating "feature" to have inside a text document.

All you need to understand is the difference between text and numbers.
If you use the formatting feature in a text table it makes no difference if you enter
10/
10/10
10 oct
10/october
or anything like that.
The cell will show
Sunday, October, 10 2010 if you formatted the cells accordingly.
In case of text you would have to type the whole formatted date string, the table would sort alphabetically rather than by date, you could not use functions like MIN and MAX to get the earliest and the latest date.

[acknak]

The behavior in a Writer table is controlled by a different option. Right-click in the table and choose: Number recognition: OFF.

Changing Writer to leave this option OFF by default is getting some serious consideration.

[datakid]

I have changed the format of the cell I want text in to text as advised.

Despite this, OO is insisting on auto-correcting the text I entered.

Please advise.