[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

 Number Formats and Locale Settings Goto page 1, 2, 3, 4  Next
Author Message
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Thu May 24, 2007 3:20 am    Post subject: Number Formats and Locale Settings

 huwg wrote: Why doesn't Calc format it "Text" even though it plainly thinks it is text?

Calc knows two types of data: number and text. A formula may return an error as a third type. Your input may be either one of formula, number or text. When you enter something into a cell it is either a formula with leading "=" or it can be interpreted as numeric input (decimal, date/time, true/false, percent, scientific 1.3E2) according to the locale of the cell's number format. If it can not be interpreted as formula nor number then it has to be text.
YOU can enforce text with a preceeding apostroph, which won't be part of the text-value. YOU can enforce text for new input through number format "Text", which won't affect existing numbers nor formulae. Automatic assignment of text-format on every typo would be too annoying and pointless since a wrongly typed number is text anway.
A cell with any kind of US number format (except for "Text") tries to guess your input like this:
12.3 = Decimal number --> 12.3
12,300,400 = 3-digit blocks separated by thousands separator -> 12300400
12,3 = Comma is not a common US date separator, so it is not a number at all -> Text 12,3
12/ = Integer, followed by a date separator -> this month's 12th day
12/31 = 2 integers, separated by a date separator -> 31th day of this year's 12th month
31/12 = 2 integers, separated by a date separator -> 12th day..., fails due to 31th month -> it must be text
true = boolean true -> 1
false = boolean false -> 0
Try with times, percent and scientific notation in unformatted cells.
If your input is seen as a "special number" in the context of the cell's number format language and there is no specific number format applied, then your numbers get some appropriate default format according to the number format locale.
All this input-guessing occurs before formatting.
Prove of concept: You can format a cell with number format locale "US", but apply british "DD/MM/YY", which leads to the confusing consequence that 31/12/07 is not a number, whereas 12/31/07 is a number displayed as 31/12/07.
What does number format locale "Default" mean?
If no number format locale is set explicitly through cell style nor hard formatting then the OOo locale is applied.
If that one is "Default" too, the language of operating system is used.

EDIT: At this point of reading you may want to continue with later posting about Menu:Tools>Options>Language Settings>Languages

Sounds reasonable -- somehow? Notice this odd behaviour:
If your input happens to be a formula, the number format's locale is not relevant anymore.
Currently I have opened a completely unformatted sheet with number format locale English(US), but working with a german application locale. The application's locale is the second setting in Tools>Option>language settings>Languages. Germany uses comma as decimal separator, true/false is wahr/falsch, days are commonly written before month with point as thousands separator as well as date separator in default date 31.12.2000. Everything works as described above unless I use formulae:
=1.2 -> #NAME [unquoted 1.2 is not a number nor a reference]
=1,2 -> 1.2 [number according to application's locale, formatted as US number]

Conversion of text-values by function VALUE:
'1.2 -> Err:502
'1,2 -> 1.2
'true -> Err:502
'false -> Err:502
'wahr -> 1
'falsch -> 0
'12/31/2000 -> Err:502
'31/12/2000 -> 36891 which is the correct date when you format the number.
When I change the application locale to English(US) and enforce recalculation (Ctrl+Shift+F9) the results toggle (errors become values and vice versa).

I raised issue http://www.openoffice.org/issues/show_bug.cgi?id=72640 on this because it is so inconsistant and makes it hard to edit multi-lingual spreadsheets having one column 12,23 EUR and another one USD 12.23. You can't use your num-pad anymore.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org

Last edited by Villeroy on Tue Mar 18, 2008 7:28 pm; edited 5 times in total
huwg
Super User

Joined: 14 Feb 2007
Posts: 890

 Posted: Thu May 24, 2007 6:49 am    Post subject: Thanks. That's another step towards me understanding all the ins and outs of Calc formatting. Edit: Note, thread split from http://www.oooforum.org/forum/viewtopic.phtml?t=57474Last edited by huwg on Thu May 24, 2007 11:43 pm; edited 1 time in total
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Thu May 24, 2007 7:44 am    Post subject: Basically it is a matter of data types text or number since all number formats are pointless if the value is not a number at all. All formatting is completely up to you, unless you leave a cell's number format unset, In this case (no number format set) Calc applies some default derived from the cell's number format locale. It shows up to 5 decimals, applies some date and/or time, true/false, percent (12.5%->formatted 0.125), currency (13.99\$ ->formatted 13.99), scientific(2.34E2 ->formatted 234, fraction(12 1/8 ->formatted 12.125). If these defaults are too annoying, just create some cell-styles (named set of format properties) and save the most frequently used in your default template. - A doubleclick on a style sets the formatting of the current (multiple) selection according to all settings defined in the style. - When anything behaves ugly, Ctrl+Shift+Space resets the formatting to the underlying style. - If you want to change something for all equally styled cells in this document, simply change the underlying style without the need to search for formatting. - Need a variant of one of your complex styles? Create a new style based on that one and change only what needs to be different. - For conditional formatting and calculated formatting through function STYLE you need styles anyway. - You can still apply hard formatting as exceptions to the rule. - A constant text value (input rather than a formula's result) can have portions of formatted text, independent from all cell formatting._________________Rest in peace, oooforum.org Get help on https://forum.openoffice.orgLast edited by Villeroy on Wed Jun 06, 2007 3:11 am; edited 1 time in total
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

Posted: Mon Jun 04, 2007 12:12 am    Post subject:

I thought I'd follow up another thread on setting a default date format. From that thread:
 Villeroy wrote: noranthon wrote: My understanding is that the date format is determined by the locale setting See http://www.openoffice.org/issues/show_bug.cgi?id=72324 You mean the system locale, the second option in the language settings? Well, this is what I thought until I had a long discussion with one of our russian members. Now my understanding is that all formatting is up to you. First, the value has to be a number at all. Number formatting on text is pointless. What is considered as number depends on the cell's individual number format locale (NFL). If input is a number (1.23, 31/12/2000, 23E-4, 13%, true) it will be formatted according to your formatting. If you did not apply any then your input gets formatted automatically according to the cell's number NFL. You set the default NFL for newly created documents through the third setting in the options-panel. Of course this will not affect your custom templates. If one cell has number format "General" with NFL en-GB 31/12/2000 is a number 12/31/2000 is text. The next cell with US-NFL handles this the other way round. "DD/MM/YY" explicitly set in a US-cell is a custom number format (in a british cell it's default date). "DD/MM/YY" with US NFL treats input 31/12/07 as number and shows 12/31/07, whereas input 12/31/07 is not a number at all. So don't do this except for proof of concept. What is pzzling me is the fact that the system locale is used in formula context. Let system locale be en-GB or alike and cell's NFL en-US: 31/12/07 is a number =DATEVALUE("31/12/07") returns an error (502: invalid argument) =DATEVALUE("12/31/07") -> 39447 (correct number of this date) With german de-DE system locale (comma is decimal separator) and some english cell-NFL: 1.2 is a number but in a formula you have to use =1,2 (comma) =1.2 will be recognized as date-input since the dot is common date-separator here. So you have to take care in multilingual documents. You may have the problem that the decimal-separator on the num-pad works in one cell and fails in the next one. When you type constants in formulae the num-pad separator should always work (I believe).

I created a new spreadsheet. First, I changed the language setting for the document to German (Germany) for the document only. That seemed to make no difference.

Modifying the default cell style did. F11 >right-click on "Default" and select "Modify". On the Numbers tab, change the language to German (Germany). The setting affects only numbers, NOT formulas:
1. Enter 1,6 --> shows in the input line and displays as the number 1,6 - German format recognised and applied.
2. Enter 1. -- > shows in the input line as 01.06.2007 and displays as 01.06.07 - German format recognised and applied.
3. Enter =1,6 --> shows as such in the input line and displays #NAME? - German format NOT recognised and an error results.
4. Enter =1.6 -->shows as such in the input line and displays as the number 1,6 - German format NOT recognised. Input applied according to Anglo-American format and translated to a German-formatted result.

_________________
search forum by month
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Mon Jun 04, 2007 8:15 am    Post subject: norathon, Yes, your're right (of course). Setting the number format locale (NFL) of cell style "Default" changes the NFL of all cells that are not using another explicitly set NFL. With a German NFL you get exactly the behaviour I tried to explain in this thread. Your inputs 1. to 4. confirm my thesis so far. However, I was wrong in thread How to set default date format in ooCalc ? where I stated, that you can set the default NFL through Tools>Options>Language Settings>Languages:"Default for Documents". I'm sorry for that. You will not get German NFLs this way. But a double-quoted string in your german document is „quoted this way“, right? If we set Tools>Options>Language Settings>Languages:"Default for Documents" to “German” then we get „quotes in this way“ and German spell checking (if availlable). So the document locale influences the char locale only (tab "Font" in cell formatting dialogue) leaving the NFL untouched. I can't decide if this is a bug or a feature. EDIT: It is neither a bug nor feature. It's just natural. See next chapter below at 5.1. Spreadsheet cells with different char locale behave just like portions of text in other documents of type Writer, Impress and Draw: „Dies ist ein deutsches Zitat“ “This is an English citation” This is English text with a differently formatted portion of German text. I set the character style of the following portion to German locale: „Dieser Satz ist in deutscher Sprache.” This sentence is English again and both languages used in this cell pass spell checking. ¿Hablamos Español? ¡Hay que instalar un diccionario! This Spanish cell is not spell-checked until I install a Spanish dictionary and restart the office. The spell checker starts croaking behind the "!" We can set the char locale to "[None]". This is a handy feature if we need to avuid all spell chekking and "typographic quotes". I had to edit the next part heavily. I stated that a style's NFL, if set "Default", defaults to it's parent style. Plain wrong. I replaced the second half of this posting with something new, based on the dialogue Menu:Tools>Options>Language Settings>Languages: [more or less from the viewpoint of a Calc user] 1. User interface: Well, this is obvious and clear. If there are additional language packs installed you can modify the entire user interface. This will not change any aspect of already existing documents nor templates. However, newly created objects of brand new documents (not from templates) will be named differently, for instance German "Tabelle1" instead of English "Sheet1". 2. Locale: Could be described as "locale of the programming environment". It determines how values are converted from text to number and back in Calc-formulae (VALUE("1.23")), Basic-IDE (cStr(Now()) and Writer-cells(=SUM ). (elsewhere?). This is the one and only locale for all language-dependent conversions in all opened documents and in the Basic-IDE. While you can set any number format locale or char locale respectively for all kinds of paragraphs, text-portions, cells and boxes individually, this is the one used for dynamic conversions from text to number and vice versa. Also this is the default language for all numbers in un-localized objects such as numeric input in a sheet-cell with number format language "Default". If this locale itself is set to "Default" the locale of the operating system is used (which is not necessarily the user interface language). 3. Decimal separator key: [x]Same as locale setting If set, the num-pad separator is interpreted as decimal separator according to the above set locale. If not set, the num-pad separator is interpreted according to the operating system. Again, this can't (and shouldn't) change the decimal separator of any given locale. It just lets you customize the char which is sent to the application when you hit the num-pad's decimal key. 4. Default currency: If no currency is specified in a cell, this one will be used. A completely unformatted cell in a german environment (locale) can take input "1€" as valid numeric input according to German number format locale. This input will automtically set a currency format. It shows "1,00 €". If I set my default currency from "Default" to "US\$" I can type "1\$" into an unformatted cell which will be formatted to \$1,00 (still using default German number format locale with comma). When I choose a currency number format in the formatting dialog this default currency gets pre-selected. 5. Default languages for documents: 5.1 Western: [None] or some language, no default. This option alone does not do anything with already existing documents nor templates. It sets the char locale ("Font" tab) for all styles of all brand new documents, not created from template. In combination with next option 6. it sets the char locale ("Font" tab) for all styles of an existing document (the current one). Glitch: The label of this option implies that there is a default, so you can unset a char locale in order to use the one set here. I could not find any object where you can set the char locale to "Default". This may be the reason why this setting does not apply to number format locales. Thinking about it, I come to this conclusion: Whenever you write text in western language, the text is supposed to be written in a distinct human language. Sometimes you may prefer [None] for code or other kinds of written art. A "Default" language for written text would imply that an English text, edited by a German co-worker is spell-checked in German and applies double-quotes like „this“. That would be an obvious design flaw. Contrary to western language, a number format language may be set to "Default" in order to show numeric symbols (figures) according to any reader's language. [not shure about asian languages, so I used the term "western language"] 5.2. Asian: 5.3. CTL (complex text layout): Any Asian, Arabic, Hebrew readers out there? 6. [x]Current document only: Changes all char locales of the current document's styles to the above settings below point 5. A quick test with some sheet-cells and portions of text shows that hard formatting still overrides styles. Hard formatted char locales are not affected by this change._________________Rest in peace, oooforum.org Get help on https://forum.openoffice.orgLast edited by Villeroy on Wed Jun 06, 2007 5:35 am; edited 12 times in total
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

Posted: Mon Jun 04, 2007 7:52 pm    Post subject:

 Villeroy wrote: So the document locale influences the char locale only (tab "Font" in cell formatting dialogue) leaving the NFL untouched. I can't decide if this is a bug or a feature.

In my "Options" tab, the option is "Default languages for Documents" and you can select "For the current document only."

So, altering the language for the document alters the char locale and the formula locale but NOT the number locale. Altering the number locale does NOT change the locale of numbers included in formulas. There seem to be two anomalies there which, to my mind, are bugs. Someone will probably claim they are features but I would ask them (if I had more patience) to explain their purposes and how they enhance the application.

If I change the locale setting to German, =1.6 enters as =39234 and displays as 39234. If I format the cell to "TT.MM.JJ", the display changes to 01.06.07. The locale setting changes seem to be consistent.

The option "Decimal separator key - Same as locale setting ()" seems to be just window-dressing. It seems to make no difference whether I select that option or not.
_________________
search forum by month
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Tue Jun 05, 2007 5:23 pm    Post subject: I had to rewrite the second part of my previous posting because I found made some mistake where i should have known better. Now the second part is based on the language options. I hope this helps to understand the how and why. But unless we discuss these issues with the developers or understand their sources we keep walking on thin ice._________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

 Posted: Tue Jun 05, 2007 6:44 pm    Post subject: Villeroy, if I had your grasp of the subject, I would raise the issue on the Calc users' list._________________ search forum by month
jsauceda
Newbie

Joined: 12 Jun 2007
Posts: 3

 Posted: Tue Jun 12, 2007 12:16 pm    Post subject: off? is thier a way to turn all formating off?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Tue Jun 12, 2007 12:40 pm    Post subject: Re: off?

 jsauceda wrote: is thier a way to turn all formating off?

Just do not format. Type plain numbers without currencies,
39234 instead of 2007-06-12,
0.25 instead of 6:00 am,
0.5 instead of 50%,
1 and 0 instead of true and false,
...
If you still prefer human readable input "2007-06-12 10:27 pm", you get the real unformatted number 39245,93598 when you remove all auto-formatting (Ctrl+Shift+Space) after input.
In formula context use =N(NOW()) instead of =NOW().
Or try the paradox. Supress all number formatting through a number format like this: 0.000.
All this "non-formatting" should not change anything except for appearance.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

Posted: Tue Jun 12, 2007 12:45 pm    Post subject: Re: off?

 jsauceda wrote: is thier a way to turn all formating off?
Yes, but you don't want to go there.
What do you want to achieve?
• Remove row/column/cell formatting and go back to default format? Mark the entire worksheet, then select "Format - Default formatting" (or press ctrl+shift+delete).
• Copy/paste only content, inheriting the formatting of the target? Use "paste special" (ctrl+shift+V if pasting into OOo document).
• Something else?
jsauceda
Newbie

Joined: 12 Jun 2007
Posts: 3

 Posted: Tue Jun 12, 2007 1:51 pm    Post subject: I need to copy and paste 16 digit numbers. When I paste into calc puts it into scientific. even when I use ctrl+shift+V for unformated paste it still puts it into scientific.
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

 Posted: Tue Jun 12, 2007 2:07 pm    Post subject: AFAIK, Calc numbers have only around 15 digits precision, so your numbers will be rounded upon entry. If you need this precision to the digit, consider using other mathemathical tools. If it's not numbers used for calculations, format the cells as text. Assuming this is numbers and not text, what you need is not default format (which is automatic) but specific formatting. Select the cells to be formatted, and then "Format - Cells" in the menus. Enter the format code #.
jsauceda
Newbie

Joined: 12 Jun 2007
Posts: 3

 Posted: Tue Jun 12, 2007 2:44 pm    Post subject: OH I SEE! Ctl+A > Right click > Format cells > Text > OK Thank you!
mfa-oo
Newbie

Joined: 10 Jul 2007
Posts: 2

 Posted: Tue Jul 10, 2007 5:03 pm    Post subject: Fraction entries treated as zero If I enter a number as a fraction -- e.g., 24 1/2 instead of 24.5 -- it appears that it is treated as zero for computation. I'm new at this, so I'm treating every "different than Excel" behavior as notable, if not a bug.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 HoursGoto page 1, 2, 3, 4  Next Page 1 of 4

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group