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

Bug? Any number entered becomes a date

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
rylan76
General User
General User


Joined: 19 Jul 2005
Posts: 10

PostPosted: Tue Jul 19, 2005 11:11 pm    Post subject: Bug? Any number entered becomes a date Reply with quote

Hi

I have had enormous trouble simply entering a decimal number into a cell. I am running OO on Fedora Core 3.

Entering 154.12 in -any- cell on a worksheet results in

54/12/01

being input in the cell when I just want a simple 154.12 value to be recorded.

I have tried right-clicking on that cell, selecting "Format Cells" and selecting "Number". This converts the value to -637378 (?) but, once again, typing 154.12 in the cell (which is now -supposed- to be a number cell, NOT a date, right?) gives the totally wrong 54/12/01. Going back to Format Cells now reveals that, once again, the cell is a date, not a number. Bug?

How can I -STOP- OO calc from doing any automatic conversions in any cell? Yes, I know about the use of the format cells function (See above) and I see that there are many date formats defined with fullstops in the template - how can I DELETE or deactivate these templates so OO calc stops treating a decimal point (.) as a DATE indicator?
The delete button is greyed out - I want NO auto-formatting done according to cell contents at ALL.

How can I do this?

Thanks!
Back to top
View user's profile Send private message
atd
General User
General User


Joined: 02 Jul 2005
Posts: 30

PostPosted: Wed Jul 20, 2005 1:31 am    Post subject: Reply with quote

Maybe you must write 154,12.
Back to top
View user's profile Send private message Visit poster's website
rylan76
General User
General User


Joined: 19 Jul 2005
Posts: 10

PostPosted: Wed Jul 20, 2005 1:55 am    Post subject: Reply with quote

Hmm, this seems to work, but calculations are still totally screwed up - trying to calculate on a field containing a "comma'ed" number, results in totally wrong answers.

For example, specifing =0.4*(A1) where A1 contains this "comma'ed" decimal number (lets say A1 contains 80,5) results in 2947668,5 as an answer...
Back to top
View user's profile Send private message
maxqnz
Super User
Super User


Joined: 24 Mar 2003
Posts: 1290
Location: Te Ika a Maui, Aotearoa

PostPosted: Wed Jul 20, 2005 2:01 am    Post subject: Reply with quote

rylan76 wrote:
Hmm, this seems to work, but calculations are still totally screwed up - trying to calculate on a field containing a "comma'ed" number, results in totally wrong answers.

For example, specifing =0.4*(A1) where A1 contains this "comma'ed" decimal number (lets say A1 contains 80,5) results in 2947668,5 as an answer...


This does sound like a locale issue, but of course, the sum would have to 0,4 *80,5, not 0.4 etc.
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3
Back to top
View user's profile Send private message Visit poster's website
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Wed Jul 20, 2005 2:39 am    Post subject: Reply with quote

Try following some of the other threads on this subject like http://www.oooforum.org/forum/viewtopic.phtml?t=5590

To set the locale appropriately goto Tools Options Languages ... male sure that your decimal separator is set to "."

The date format settings have NO effect whatever on how the input to cell is recognized only on how it is output
Back to top
View user's profile Send private message
rylan76
General User
General User


Joined: 19 Jul 2005
Posts: 10

PostPosted: Wed Jul 20, 2005 6:03 am    Post subject: Reply with quote

Hi David

Thanks for replying - I checked out the thread you suggested and I have tried almost exactly what you suggested to that poster. My problem is that Calc dos -not- respond to any non-date format selection for a cell, neither can I specify . as a decimal seperator. It seems that all selections I make to try and set a formatting in a cell (or on a whole sheet) are ignored by Calc.

I have guessed that to stop Calc treating a number like 152.42 as the year 2152 2nd April, I need to remove the

D. MMM. YYYY

format code in the "Format Cells" dialog, or change the . to , which I have tried to do several times - Calc seems to accept the change, but the next time I open the dialog the format is exactly back as it would be if I made no change. I cannot remove that format line either, since it it apparently built-in and the X (to delete a format line) is greyed out.

That's the whole nub of my problem - no matter what I do I can't get calc to stop treating every single number I input -anywhere- as a date... Even inputting numbers with , as a decimal seperator does not work since cell arithmetic is then screwed up, and doing something like =(0.4) *(A25) where A25 contains, say 10,2 results in a totally impossbile six-digit gibberish answer.

Any idea what gives?

Thanks!
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Wed Jul 20, 2005 3:10 pm    Post subject: Reply with quote

Have you checked your Locale setting? This will determine how OOo expectes dates to be entered.

dfrench wrote:
...To set the locale appropriately goto Tools Options Languages ... male sure that your decimal separator is set to "."

The date format settings have NO effect whatever on how the input to cell is recognized only on how it is output
(I haven't checked the referred thread, so forgive me if you have already checked this)
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
rylan76
General User
General User


Joined: 19 Jul 2005
Posts: 10

PostPosted: Wed Jul 20, 2005 9:59 pm    Post subject: Reply with quote

Hi Dale

I checked - it is set to "Default" (whatever that is). I've tried several languages (UK and US English, plus South African English) but the problem still remains exactly the same - any number input in any cell becomes a date, and it is impossible to set any cell to anything BUT a date...

Any ideas at all...?

Thanks a lot!
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Wed Jul 20, 2005 10:53 pm    Post subject: Reply with quote

rylan76 wrote:
Any ideas at all...?
Er - no.

It sounds like a locale problem. The following suggestions are me clutching at straws.

What version of OOo are you using? If you have already specified, please forgive my poor eyesight. (This won't help me help you, but it might give someone else some ideas)

David mentioned to make sure your decimal separator is set to "." - I can't see where to do that. It might be specific to OOo on a *nix system, or he might mean in the OS/desktop settings (don't know enough - anything - about *nix).

Try the following
    Set your locale and the default languages for documents (both) to US English (why? I don't really know except that maybe it's the most used version of English and therefore the least problematic - - - a very big assumption on my part)
    Expand OpenOffice.org, select Memory and turn off Quickstarter
    Expand Load/Save, select General and turn off "Load user-specific settings.."
    Close OOo.
    Shut down your system and cold restart (this to make sure there are no threads, open files, whatever hanging around from your last session)
    Open a new Writer document and check that your locale etc. are as you left them.
    Open a new spreadsheet and see what you get.
I really do not expect this to work. I'm only suggesting that your turn off as much as possible, use the settings that are most likely to work and see what happens.

You cold try running setup again, and choose "Repair" at the prompt.

Maybe one of the more knowledgeable contributors can suggest something that might work...
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
rylan76
General User
General User


Joined: 19 Jul 2005
Posts: 10

PostPosted: Wed Jul 20, 2005 11:34 pm    Post subject: Reply with quote

Hi Dale

Thanks for the suggestions! I'll give it a try - I am starting to suspect one of two things: either I am making a very basic mistake or there is something very wrong with the OO version / instance (or OO in general) I have on my system. Either way, I've got to go back to Excell (yuck...) and Windows (double yuck) to do what I need, since I have a deadline on the work to keep to. And I can't spend 2 hours just trying to get a simple decimal number like 152.52 into just one cell... my Boss would kill me if he found out.

Darn! And I thought I could leave Excell alone for good. Guess OO isn't quite there yet.

Thanks a lot for the detailed reply anyway!

Kind regards,
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
Page 1 of 1

 
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