Turn off date autocompletion in Calc 4.1.1

[scubscub]

So, I want to enter 10/12 into a cell, but Oo keeps changing that to 10/12/15. I have all the autocomplete/autoinput features turned off that I could find, but it still keeps changing to 10/12/15. I'm losing my mind, how do I turn this off?

Thanks for any help!

[acknak]

Taking 10/12 and converting it to a date happens at the same level as taking 10.12 and turning it into a numeric value. In other words, it's a core spreadsheet feature; it's not a special feature that you can turn off.

However, there are a couple of ways you can explicitly tell Calc to take everything you type as literal text, without interpretation.

Simplest is to prefix the entry with an apostrophe: '10/12

The apostrophe is not part of the entry--it's just a signal to Calc that everything following is text: leave it alone.

If you have a lot of such entries, then you might want to format the range (or column) as text before making the entries. Format > Cells > Numbers > Category: Text

If the cell format is text when you make an entry, then Calc will take the entry as text.

[scubscub]

How could that be a "core spreadsheet feature?" It seems obvious that if anything other than what I type ends up in the cell, this is a special feature that should be optional.

Anyway, I am entering a column of fractions, and I would like to be able to find the average. Will I still be able to do this if I tell calc to treat the numbers as text?

[RusselB]

Uncheck the setting at Tools -> Cell Contents -> Auto Input

This, in my experience, resolves the problem you are describing, though it does have the effect of also turning off the balloon prompts that show when entering functions.

Using Acknak's recommendation would require additional work for Calc to recognize the numbers, entered as text, as numbers for mathematical purposes.
Note that turning off the setting I have mentioned will require you to re-enter the numbers that are showing incorrectly. Just editing them will not be sufficient.

[scubscub]

Hi Russel,

Huh, I do have Auto Input unchecked. I even tried checking and unchecking it, but the date still autocompletes.

[MrProgrammer]

So, I want to enter 10/12 into a cell, but Oo keeps changing that to 10/12/15. I have all the autocomplete/autoinput features turned off that I could find, but it still keeps changing to 10/12/15. I'm losing my mind, how do I turn this off?

In other words, it's a core spreadsheet feature; it's not a special feature that you can turn off.

Read section 5. Understanding data entry in Ten concepts that every Calc user should know. This will explain what's happening with this core spreadsheet feature.

Anyway, I am entering a column of fractions, and I would like to be able to find the average. Will I still be able to do this if I tell calc to treat the numbers as text?

No. Instead, you should use Format → Cells → Number → Format Code → ##/##. That will prevent 10/12 from being interpreted as a date. Well, it will prevent it in the English (USA) locale. I am not familiar with how other locales treat data entry. It would be even better to use styles instead of Format → Cells.

Edit: The OP probably wants Number → Format Code → ##/## or maybe ###/### instead of my earlier suggestion of Number → Fraction

So, I want to enter 10/12 into a cell …

I trust it will be OK if Calc displays that fraction as 5/6. It will require extra work (storing values as text and then converting them to numbers as RusselB suggested) if you insist on seeing the fraction 10/12 displayed exactly as you entered it. Read the tutorial to understand the difference between stored values and what is displayed in the cell. If the value is to be stored as a number (read 1. Types of data in the tutorial), it will be 0.833333… and Calc has no way to know that you want that value displayed as 10/12 instead of 5/6 or 100/120. Calc will use 5/6 since that is the fraction in lowest terms.

I even tried checking and unchecking [AutoInput], but the date still autocompletes.

AutoInput is a different feature. Turning it off won't affect the locale interpretation that is explained in the tutorial above.

I'm losing my mind, how do I turn this off?

To avoid future frustration, you will want to study the complete tutorial until you understand it.

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.

[scubscub]

Thank you, this solved the date-autocomplete problem for that column. Unfortunately, it is now changing 10/12 to 5/6.

I know this may be picky, but I want it to read 10/12 because that represents that a student turned in 10 out of 12 assignments. Is there a way to prevent fractions from simplifying?

[RusselB]

The only way to get Calc to not turn your fractions into lowest terms is to use some text functions.
The easiest way will require some extra work on your part, as you'll have to enter the numerator in one cell and the denominator in another.
Eg: in A2 enter 10
in B2 enter 12
In C2 enter

Code: Select all

=text(A2;"#")&"/"&text(B2;"#")

or

Code: Select all

=concatenate(text(A2;"#");"/";text(B2;"#"))

NOTE: These "numbers" will need another conversion for any actual mathematics to be performed.
You might be smart to use D2 with

Code: Select all

=A2/B2

and then hide column D.
If you need to do mathematics, you can manually reference the cell(s) in column D for the actual mathematics.

[MrProgrammer]

The easiest way is to store 10 and 12 in separate cells. Read the material about 0.833333… above.

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.

[scubscub]

Okay thanks. Since every entry in the column is out of 12, I'll just enter 10 and remember that it is out of 12. Thanks for the help, everyone.

This is an annoyance, though. If there is no simple way to get "What you type is what you get" in calc, then that is a serious design limitation.

[RusselB]

You're more than welcome to look at the source code and figure out what needs to be changed for Calc to operate the way you're wanting.
The last time I worked with a spreadsheet that didn't reduce fractions to lowest terms, by default, was back in the days of DOS 5.0

[scubscub]

I imagine most people want their fractions to reduce by default, just like most people want spellcheck and autocomplete. But the latter two can be turned off, thank goodness.

[MrProgrammer]

If there is no simple way to get "What you type is what you get" in calc ….

It is simple: Format → Cells → Numbers → Text

… then that is a serious design limitation.

Computer-based spreadsheets have been around over 50 years. If you read about their history, you'll find that the design goals were different than you imagine them to be. The operation of spreadsheets has not changed much in over 20 years. I'm not aware of any spreadsheet implementation that offers display of values exactly as entered, unless they are entered as text. The tutorial explains why spreadsheets operate as they do. If spreadsheets don't meet your needs, then don't use them. If you want to use spreadsheets, learn how they work. A spreadsheet which attempted to please everyone would be so impossibly complex that no one could use it.

Since every entry in the column is out of 12, I'll just enter 10 and remember that it is out of 12.

In that case use Format → Cells → Numbers → Format Code → 0"/12" and remember to divide by 12 as needed.

[scubscub]

MrProgrammer, I sincerely thank you for your help on this forum, and for taking the time to answer my questions. For doing these things, you (and the others) are like an angel to me. That is not irony.

I do think that witiwyg is a powerful, basic design principle that calc unfortunately seems to ignore completely. I don't think it would be that difficult to implement (if eg calc's behavior of checking all entries to see if they are dates was toggleable). Witiwyg was (obviously) a feature of the paper technology which these programs are based on. Witiwyg also means that no matter what you see in the cell, you cannot blame it on the programmer, or complain to customer support...

[RusselB]

Witiwyg?
Did you mean WYSIWYG?

[acknak]

... you should use Format → Cells → Number → Format Code → ##/##. ...

Oops. Thanks for providing a better answer.

I keep forgetting that the fraction cell format is one that also changes the interpretation rules.

Since the denominator is constant, I would tend to enter only the numerators, as integers, and then perform the division as needed.

I do think that witiwyg is a powerful, basic design principle that calc unfortunately seems to ignore completely. ...

Sorry, I think this has to be the top misconception when it comes to using a spreadsheet: spreadsheets are absolutely not WYSIWYG--and have never been.

Excel, and other, earlier spreadsheets, work the same way. I wouldn't say it's a good design but it is practical: entering dates must be far more common than entering fractions, and (in the US) the formats happen to be identical. "10/12" is ambiguous in that case and Calc implements the more common entry scenario. Since you want something different, you have to take an extra step.

[jrkrideau]

So, I want to enter 10/12 into a cell, but Oo keeps changing that to 10/12/15. I have all the autocomplete/autoinput features turned off that I could find, but it still keeps changing to 10/12/15. I'm losing my mind, how do I turn this off?

Thanks for any help!

For a lot more information than you probably want look at my last post (well last as of 2015/05/20 08:30) at viewtopic.php?f=9&t=77248#p352758

More simply, format every cell that you are likely to put a 'real' fraction into as fraction

Format > Cell > etc. Note you can still and integer and real numbers although it affects formatting slightly.