OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Number Formats and Locale Settings
Goto page Previous  1, 2, 3, 4  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Alan2Po
Newbie
Newbie


Joined: 30 Mar 2008
Posts: 3

PostPosted: Sat Apr 12, 2008 11:20 pm    Post subject: Reply with quote

Villeroy wrote:
No, Latvian locale implies comma, no matter how you tweaked your operating system's Latvian locale.


Then this is an error. it makes calc unusable with Latvian locale. why not take windows settings?
Back to top
View user's profile Send private message
Alan2Po
Newbie
Newbie


Joined: 30 Mar 2008
Posts: 3

PostPosted: Sun Apr 20, 2008 3:54 am    Post subject: decimal symbol Reply with quote

And hope this error will be correced.
Back to top
View user's profile Send private message
JJJoseph
Power User
Power User


Joined: 25 Sep 2005
Posts: 81

PostPosted: Mon May 05, 2008 12:31 pm    Post subject: Re: off? Reply with quote

Villeroy wrote:
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,


This is too crazy-making. Is there any way to force all dates into a fixed format? I don't really care how Europeans use dates, but it seems like I have to be aware of date variants (to the extent of inputting "39234 instead of 2007-06-12").

Perhaps this would solve the associated problem of using date functions (which don't work unless you know what format Calc is expecting as input). Calc does not reveal what date format it's expecting, and I'm still not sure what "07/08/09" (example) represents. I'm unable to figure out date functions even after wrestling with Calc for several years.

What a mess!
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Wed Jul 09, 2008 5:24 am    Post subject: Re: Fraction entries treated as zero Reply with quote

mfa-oo wrote:
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.


Calc is treating that as a text entry with is actually reasonable as there is a space between 12 and 1/2. If you want the number formatted as 12 1/2 you can enter the number as 12.5 and format the cell as fraction.

Not as convenient as in Excel but probably cuts down on errors.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
John_A
Newbie
Newbie


Joined: 04 Apr 2009
Posts: 1

PostPosted: Sat Apr 04, 2009 6:05 am    Post subject: Reply with quote

Hi folks.

The locale issue is quite contentious, and I don't want to offend anyone, but I have a few comments and questions. My focus is the EN ZA (English South Africa) locale.

Who sets the locale defaults in the XML file? Can the information be traced back to a standards organisation (the SABS, in my case), or is it supplied by a "knowledgeable volunteer", from his/her own experience?

My reason is this: the thousands separator listed in the XML file (and in the localedata_en.dll file, obviously) is "," while several hundred thousand documents published here use " " as a thousands separator. There may be industries where the "," is used, but similarly, there are industries here with their own standard way of presenting data. For example: the financial industry uses " " exclusively, including the big four auditors, publishing thousands of financial statements annually, thousands of accountants, and SARS, our "inland revenue department".

With such a large base of (frustrated) users, is it possible to amend the default to " ", rather than ",", or at least publish a new "English South Africa - Finance" locale for these users (A very high percentage of OOo users, probably more than 50%, I'd guess)?

What is the accepted way of dealing with this problem. Surely other countries have multiple industry standards, if not multiple technical standards.

Another option would be for users like myself to choose an existing locale which conforms to our industry standard. Unfortunately, I have found none (after examining dozens), as the locales which use " " as a thousands separator tend to use "," as a decimal separator, while we use ".". Is there any quick table available showing the various locales and their defaults?

Hopefully I can resolve this issue without having to hack the localedata*.dll files, or compile a custom build. (I'm a noob at that).

Thank you for your time,

John.
Back to top
View user's profile Send private message
r.johnson
General User
General User


Joined: 28 May 2009
Posts: 5

PostPosted: Mon Jun 01, 2009 2:49 am    Post subject: Reply with quote

Villeroy wrote:
Do you believe that any spreadsheet program other than Excel can be acceptable for you?


You're really snide. Stop helping people. You're probably driving more people away than anything else.
_________________
This is my signature, not very interesting is it?
Back to top
View user's profile Send private message MSN Messenger
darice
General User
General User


Joined: 02 Jan 2010
Posts: 15

PostPosted: Sat Jan 02, 2010 7:31 pm    Post subject: Turning off Automatic calculations Reply with quote

Hi all, Sad

Im very new to this and not as advanced as you all appear to be I need someone to explain really simply how I can switch automatic calculations off.
I want to write 11am it keeps coming up with 11:00:00.
I dont want it to automatically write 11:00am either because i understand how to get it to do that I just want it to simply say 11am.
I am so frustrated and sick of trying.
I would really appreciate some help.
Thanks Darice
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jan 02, 2010 11:15 pm    Post subject: Reply with quote

Do not use spreadsheets at all.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Mar 01, 2010 7:36 am    Post subject: Reply with quote

Short abstract
All Excelish spreadsheets of the past 2 decades behave more or less in the same way. If you can't handle this, then you must not use spreadsheets [I never get tired to recommend databases where you can enforce almost anything -- if your can].

0) Every cell has a number format locale. Number format locale "Default" falls back to the application wide locale in the language options. Most importantly, the locale context determines the decimal separator (comma or point) and if 12/1/1999 means 12th of January or December the 1st (US).
1) All your input is evaluated as some type of number within the locale context. The value is text if this evaluation fails.
2) With a leading = your input is evaluated as number. The result is some error value if the evaluation fails.
3a) A leading quote inhibits all evaluation. All input is literal text. Text "123" is not a number. It is a sequence of characters like "abc" is.
3b) Like any other formatting attribute (color, font, border,...) number format "@" (text) has absolutely no influence on existing data, BUT all new input will be treated as in 3a).
4) All your numbers will be shown in one particular number format, namely the number format you have applied. If you do not apply any number format (number format "General") then your input is shown in one particular number format too. The particular number format depends on the locale and the type of number you typed. There is one particular number format for integers and floating point numbers, one for percent input, one for date input, one for time input and for date+time input, there is one for your input of a scientific number and a one particular boolean number format showing TRUE and FALSE in the language of the cell's locale.
There is no way to modify these defaults.
One particular number format is the key which makes your documents portable across language boundaries and office versions.

It is completely pointless to complain about this or that number format. Number formatting is not important at all as long as the values are the right ones.
Many confused beginners expect some auto-formatting which formats your numbers exactly like you typed them. This is not possible, not even desireable. You could hardly tell if your input has been taken as number or text.

Convert numbers to text and text to numbers:
menu:Edit>Find&Replace...
[More Options]
[X]Regular expressions
[X]Current selection only
Search: .+ (dot and plus)
Replace: &
[Replace All]
This replaces every value with itself. If the cell is formatted as text, the input turns into text, any other number format turns the entry into a number.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Lino
Newbie
Newbie


Joined: 08 May 2010
Posts: 1

PostPosted: Sat May 08, 2010 1:36 am    Post subject: Reply with quote

Yeah, forced date formatting is annoying. More than the Orange.

As much as I can see, the problem is in formatting order. It is:
1. try in any case to make input a number
2. if not possible, make it text or something

The solution would be to stop forcing number formatting before date format and make it text. Date formatting apply only if cell formatted as date.

Any idea how to make it?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat May 08, 2010 1:49 am    Post subject: Reply with quote

Quote:
Any idea how to make it?

You may be able to change the overall number formatter in the source code. If you are clever enough, you won't break the affected parts of this integrated office suite.

Millions of spreadsheet users are able to use a spreadsheet as it used to be since decades.
Simply enter text if you mean text or use a database application to collect your data in a consitent manner.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
BRT1
General User
General User


Joined: 06 Jan 2011
Posts: 9

PostPosted: Thu Jan 06, 2011 10:27 am    Post subject: [Solved] Re: Fraction entries treated as zero Reply with quote

mfa-oo wrote:
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.


Ok solved it.

The solution was: I had to set the value to "date" for a wider range of cells, basically applied it to the complete sheet apart from column A and row 1.
Also checked the "default language settings for documents" under tools-options-language settings-languages to English UK as discussed in the other thread. It could have helped though the other language setting were on "english UK" already.

Well it works now. Thanks for all support.

Stephan
Back to top
View user's profile Send private message
bernard_ivo
Newbie
Newbie


Joined: 22 Jun 2011
Posts: 1

PostPosted: Wed Jun 22, 2011 12:40 am    Post subject: Reply with quote

Dear all,
I have tried to find an answer to that question, but apparenlty I couldn't. So here is what I'm looking solution to.
I'm transforming a table from .PDF to .XLS and the numbers in the PDF are formated this way:
234,456 = two hundred thirty four thousand four hundred fifty six
when the .XLS is opened in OO the number is presented
234.456 - the comma was not interpeted as thousands separator but a decimal sign.

I have played with locale seetings but the only result is that it set either comma or a dot for decimal sign and the number is not recognised as the integer for "two hundred thirty four thousand four hundred fifty six". I would like to convert the number to 243,456.00 without having to rewrite every single number in the tables. Is that possible and how?

Cheers
Back to top
View user's profile Send private message
kevin.burton
Newbie
Newbie


Joined: 28 Jan 2012
Posts: 1

PostPosted: Sat Jan 28, 2012 5:30 am    Post subject: thanks Reply with quote

now i do understand how openoffice works thanks to all your help
Back to top
View user's profile Send private message
remo00
OOo Enthusiast
OOo Enthusiast


Joined: 21 Dec 2006
Posts: 117

PostPosted: Mon Mar 12, 2012 3:16 am    Post subject: Reply with quote

I think some wrong set up exists. Try typing the following

With English(UK) locale
[comma(,)=thousand, dot(.)=decimal, slash(/)=date, column( : )=hour]
12(dot)3 is recognised as a number 12decimal3-OK-
12(comma)3 is a STRING -OK-
12(comma)345 is the number 12345 –OK-

With Greek locale
[dot(.)=thousand, comma(,)=decimal, slash(/)=date, column( : )=hour]
12(comma)3 is recognised as a number 12decimal3 -OK-
12(dot)3 is the date 12/03/2012 -WRONG should be a STRING-
12(dot)345 is the number 12345 –OK-

With French (France) locale
[space( )=thousand, comma(,)=decimal, slash(/)=date, column( : )=hour]
12(comma)3 is recognised as a number 12decimal3 -OK-
12(dot)3 is the date 12/03/2012 -WRONG should be a STRING-
12(dot)345 is the date 01/12/345 -WRONG should be a STRING-

So, when dot(.) isn't the decimal separator, all entries with numbers and dot are incorrectly interpreted as date even if the date separator are different (slash or hyphen)
In those cases number with dot should be interpreted as STRING.

Said that, I really don't know how to correct this.

Cheers
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page Previous  1, 2, 3, 4  Next
Page 3 of 4

 
Jump to:  
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