Number Formats and Locale Settings

[huwg]

That's because it's not being recognised as a number. It is being treated as text.

To enter a fraction in Calc you need to specifically format the cell as a fraction, AND enter the fraction in improper (top heavy) form. ie. 49/2 in your example.

[mfa-oo]

Thanks; I had guessed the reason, but not the solution. Probably easier in general to enter a formula to evaluate the fraction, especially where it isn't a simple one -- e.g., =24+17/47.

[huwg]

I need a bit more help understanding formatting...

• Open a new spreadsheet with no formatting. ie. Default cell style is set to Number General and all cells are Default cell style.
  
  
• Enter something that Calc will "interpret". eg 1.2.3, which on my locale is immediately formatted by Calc to 01/02/03, blue with Value Highlighting on; or 123 which is formatted as a number, right-aligned, blue with Value Highlighting on.
  
  
• Edit the Default cell style from Number General to Text @.
  
  
• Reset all cells to Default cell style - date changes to 37653 but stays blue, and 123 stays blue.

Why aren't numbers converted to text by the format change (I appreciate this would probably be bad, but would like to understand better)?

I can input text to number formatted cells with an apostrophe. Why is there no way numbers can be input to text formatted cells? (Unless I paste a number from another spreadsheet, which brings its number formatting with it).

Is there a way to access and change the underlying "type" that Calc must store for the contents of each cell?

[Villeroy]

Direct input, find&replace and several kinds of import seem to be the only ways to change data. Whatever you do with formatting, view or Menu:Tools>Options... may change the representation of data, but never ever the data themselves. I'm conviced that this simple rule is a feature indeed.
We can convert between text and number in place by changing the values through find/replace http://www.oooforum.org/forum/viewtopic.phtml?t=57743
Another approach involves functions VALUE and TEXT.

[magnoliasouth]

In Excel (gag, I know), you can format row backgrounds to stay a certain background color, despite sorting.

For example say this is my sheet:
1 Oranges (grey background)
2 Apples (white background)
3 Bananas (grey background)

I then decide to sort text from A to Z, but the colors remain:
1 Apples (grey background)
2 Bananas (white background)
3 Oranges (grey background)

As I mentioned, this can easily be done in Excel (http://www.cpearson.com/excel/banding.htm), but I cannot figure out how to do this in OpenOffice. I have applied a style, but it's not showing up. Can it be done?

[jrkrideau]

[huwg]

Data > Sort... > Options > Include formats

[magnoliasouth]

Aha! Thank you so much to the both of you.

[yunluck]

I found the OOo Calc is hard to display a spreadsheet which is done by a MS excel.It are always error code.I am from China.How to deal with that?

[jrkrideau]

[Alan2Po]

Hello.

I red posts about decimal. But got no answer.

I have set decimal symbol to "." (point) in windows regional and language options.

in calc pressing decimal symbol on keypad causes "," (comma) to appear.
in other programs pressing this button causes decimal symbol (point) to appear.

I tried to change thru language options this, but no success, comma keep coming.

is it possible for XP with Latvian locale change decimal to "." for calc ?
and how?

[Villeroy]

No, Latvian locale implies comma, no matter how you tweaked your operating system's Latvian locale.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[JJJoseph]

One of my biggest problems with the 'smart' calendar dates is when pasting text into a spreadsheet. This is easy to do with Excel, but impossible with OO Calc. Calc will change everything that looks like a date, whether it is or not. If it is a date, Calc will change it (incorrectly).

Is there any way I can stabilize the calendar dates so that they remain as text, regardless of what Calc wants to see? I'd like to have English language, America decimals, and ANSI ates (YYYY.MM.DD)., and letter-size (not A4) printing. Dates without punctuation (YYYYMMDD) are acceptable, but dates with slash separators are difficult to type (decimal separators are much faster). If I could turn off the OO smart dates, I could work much faster just using text dates with decimal separator.

Using ANSI dates removes all ambiguity, so there's no need to change the date display when viewing in different locales. Therefore the text solution is the best one, yet I can't figure out how to do it consistently in Calc.

The ideal total solution would be "locale=none" (with dates as text-only) so that the locale variables could be managed by the user.

[Villeroy]

Text is text in any spreadsheet program. Number formatting and number format locales has no effect on text values. If do not want to do any calculations with your dates and always want to type all the digits in "20081231", then simply use text values. Paste special "unformatted text" allows you to specify the import type for each column. Preformatting the target cells works as well.
However, I would accept numeric dates and simply apply my own number format, preferably through styles.

[JJJoseph]

I just noticed that, with locale="Canada", the only date format acceptable to the data input window is mm-dd-yyyy, which is incorrect for Canada. Canada officially uses the ISO8601 format (yyyy-mm-dd), and there doesn't seem to be any way to correct this bug. I also note that Germany, which also officially uses ISO8601 format, has the same bug. I guess the OO designers don't travel much!

BTW: sure, I really like Excel, but we're trying to adapt to OO because of the cost savings, so if you can help us solve these bugs, we'll be another bunch of happy OO users