Automatic corrections

[Zeta]

Hello,

I've noticed OpenOffice Calc does a lot of automatic corrections I can't seem to disable. In the only two instances I've had to use a spreadsheet application recently I've been encumbered by automatic corrections both times.

First, if the contents of a cell is in completely capitol letters, it is automatically corrected to lower case. There is no "all caps" in the font format, etc. While I'm certainly not a fan of SHOUTING in professional documents, I was trying to modify an existing .xls spreadsheet that written in all capitals and I wound up having to rewrite all of the text in the document in lower case for consistency. The automatic correction speed that process up a bit, but still, not very exciting. Is this really a necessary option?

Now I'm trying to write a spreadsheet with U.S. military times along one column and the program is correcting terms like '0500' to '500'. Frankly, '500' isn't the proper format I need and if I try 05:00 it becomes 05:00:00 AM which certainly isn't the military format I need my document written in. I can't find any time format options and, although I found some writing aid options that might have fixed the caps problem before, I'm completely stumped as to how to stop *this* autocorrection.

Can anybody help with the time format problem?

[JohnV]

WORD to Word. Try turning off Tools > AutoCorrect - two initial caps and cap to begin sentence.
WORD to word or vice versa. Select them and do Format > Change Case > Upper/Lower.

0500 to 500, etc. Format the cell(s) as Text or enter with leading apostrophe, e.g., '05:25.

[kingfisher]

If you want to enter times as values instead of text, format the column/s with a date/time format with the menu Format >Cells. Better still, create and apply a time style with F11.

While dealing with annoying automatic features, I'd switch off 'AutoInput' using the menu Tools >Cell Contents >AutoInput.

[acknak]

If you want to enter times as values instead of text, format the column/s with a date/time format...

Sorry, I'm having trouble with this; could you give an example? What format do you use and what would you enter to get the desired result?

[realestatebree]

If you were happy with 5:00 instead of 05:00, you could select your cell range, then go Format - Cells - Numbers - Time - then select the first option.
Otherwise, the option already suggested of making them text (ie: entering '05:00 in the cell) is the only other thing I can think of.
Good luck!
Bree

[kingfisher]

I get a leading zero with 24 hour time. Format code HH:MM The input line shows 05:00:00; there's nothing you can do about that but the display is 05:00

[Villeroy]

If 0515 and 2359 are military times, a user defined time format HHMM will do what you want. The application has no interface to your state of mind. You'll still have to enter 5:15, 23:59 or 11:59 pm for the same reason. If you want cells that are predefined to take time values only then you need a database with database forms. Spreadsheet applications are intentionally designed to take any type of value in all cells. This makes spreadsheets easy and complicated at the same time.
All this is not related to number formatting in the first place. It's a matter of cell types formula, number or text. Before your input gets formatted automatically (since you did not specify otherwise) the applciation makes a guess if your input is a formula (leading =) or a number. If it is not a formula nor number then it is taken literally as text value.
A set of English cells treats input "0.5" as number 0.5. Same with
"12:00"
"12:"
"12 am"
"50%"
"5e-1"
=TIME(12;0;0)
and several other input which is taken as the equivalent of decimal number 0.5 or evaluates to 0.5 (the formula). All this input generates a cell value of 0.5. If you apply a userdefined military number format "HHMM" you always get 0.5 displayed as "1200" (half a day). No matter how you try to enter the 0.5.
If you did not specify how you want the values to be shown, the application applies some default number format according to the way you have entered 0.5. This is just a convenience to enable arbitrary input without pre-formatting. If you don't like the way it looks you've got to specify things. But take one thing for shure: No formatting will ever change a numeric value nor convert number to text nor text to number. Never ever. It's an important feature. There is one special number format "Text". This will not convert neither, but it will take all new input as text, beeing numeric or with leading =. A cell with any number format but "Text" takes 0123 as text if you type it with a leading quote. The quote won't be part of the text value. Again, a text is a text and no formatting will ever change this.

[acknak]

Thanks for that explanation Villeroy, very helpful. I still find this confusing, and from the number of questions we see, I think many people do.

Can you think of a situation where the applied format of a cell will affect the number recognition--other than format="Text"?

E.g. using in kingfisher's good suggestion, setting the cell format to "HHMM" will change the way the cell's value is displayed, but you still have to input either 5:00 or 0.5 in order to enter a time of "0500" (5AM).

I imagine it as two (mostly) separate processes:
• Number recognition on input
Calc's internal programming determines this; it can't be changed by the user, and
• Formatting on output
Calc provides a default format, but the user can change it.

I type: 5:00 Calc performs number recognition: the entry is a time
Yields value: 0.5 Calc converts the time entry string to the internal decimal value
Displays: 0500 The decimal value is displayed according to cell format "HHMM"

Does that seem right?

PS: My favorite example of just how goofy this can get: enter 123 in a cell, format it as currency (toolbar button is fine). Now enter this into that cell: 1/1 The result? $39,448.00 Hmm... that seems a little off.

At least this is less likely to happen in a locale where the date separator is "/". If you happen to be working in some remote locale where "," is the decimal and "." is the date separator, it's an easier slip, and hard to see the difference: 1.1 vs. 1,1

[Villeroy]

acknak, you've got it as far as I believe that I've got it. Setting the number format locale seems to be the only way to change the way of number recognition (slightly). This works on cell level, on document level (cell style "Default") and application level (Tools>Options...Language Settings>Locale).

Yesterday on the other forum: why...why WHY???? doesn't it use my LOCALE DATE SETTINGS!??. Short description: If OOo reads the locale from the operating system then it will not read all the details. It reads "en-GB" and applies its own British setup, no matter what details you may have specified in the WIndows registry. Meanwhile I believe that this is neither lazyness nor design flaw. The question is how many settings can be reasonably saved in a document, so the same document looks the same on all systems. The defaults are the defaults - point. They are not the defaults that are currently set on your particular system. When your dates use 4 digit years, save this setting on document level, so the other application on the other system has a chance to do the same thing to make the document look and behave the same.

My personal issue with multilingual settings: When you really try do design a multilingual document with a column of German numbers and a column of English numbers then you have to type comma decimals in one column and dot decimals in the other, which is really disturbing. Furthermore the formula syntax depends on the application locale only. With English application locale, but German cell locale you have to type a formula as "=1.2" (dots) but a constant value in the same cell requires a comma "1,2". In both cases the German cell yields a comma decimal.
I wish the input method would depend on the application wide locale only (like the formula syntax does already) and the output depends on the number format locale only. http://www.openoffice.org/issues/show_bug.cgi?id=72640

OOops this was slightly off topic since the OP had no locale issue, but it is related to number formats.

[Villeroy]

Just another reply to the original poster. You can save your data in a database and use formatted controls in database forms to show and edit your time values in military format "HHMM" ("2359"). I just tried with a so called "formatted field" on a database form, linked to a database column of time values. In a database every column accepts a dedicated type of values, thus offering a completely different set of options and restrictions as well.
Spreadsheets are intentionally designed to accept all values in all cells. This requires a certain set of rules. Otherwise your highly customized spreadsheet would be unmanagable by others while the spreadsheet application would turn into a programming platform.
Proposal for your spreadsheets: Format the cells in question as "0000" (4-digit integers) and enter 4-digit numbers.
For conversion to regular time values (fractions of days) use something like this:
=VALUE(LEFT(A1;2))/24+VALUE(RIGHT(A1;2))/1440
=VALUE(LEFT(TEXT(A1;"0000");2))/24+VALUE(RIGHT(TEXT(A1;"0000");2))/1440