Issue 77973 - A better way of treating numbers/dates?!
Summary: A better way of treating numbers/dates?!
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOo 2.2
Hardware: All All
: P3 Trivial with 12 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
: 87557 106336 (view as issue list)
Depends on:
Reported: 2007-05-31 07:19 UTC by mawe3661
Modified: 2013-10-08 04:32 UTC (History)
3 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description mawe3661 2007-05-31 07:19:39 UTC
I am exporting/importing data quite a bit into/from calc and often get issues
with  the decimal separator (',' or '.'). I'm working on a Swedish system and
',' is the Swedish standard, however, lots of programs generate numbers with '.'
as decimal separator (DS) and also want that as input. So my first want would be:
* Have a option in input/output dialogs (as well as clip board inserts) where
you can bypass any other settings in OOo and choose your own decimal separator
out of (','/'.'). For import this exists in the MS counterpart.

I also find this a problem when typing numbers i calc. I some time forget what
DS I should use and write number using '.'. What then happens is that my system
interprets this as a DATE, i.e. 1.22 becomes 01.01.22. This is totally illogical
for a Swede, we never writes dates like that. That formatting should be removed. 

Coming to this, dates is a pain typing. Different countries have different ways
of writing dates, and this seems to make the built in formatter go wild. 

* If date formatting was turned off by default, and you would have to choose a
date format for the cell/range of cells, before date information is inputted,
that would save some confusing I think. The old formatter could then be used to
 recognize could-be dates when and if the cell didn't have a date format set -
show a message saying something like - please select date format for the cells
you want to input dates in before inputting the dates. 

Having implemented the above, only one case remains for actually needing to
distinguish between (','/'.') when formatting as a number. That is to
distinguish between someone wanting to input 11,22 as a string number sequence
instead of the decimal number 11.22. Again, this could be solved through a
dialogue, showing up if you have the DS set to ','. OF course, in Sweden 11.22
could also be interpreted as 11:22 (a time), but that should also be one of the
cases where the time/date dialogue above should emerge. Therefore,
* treat both '.' and ',' in number strings as decimal separators, with the
exceptions above. 

This is my suggestion, I hope it is clear enough, but please ask for
clarification if it is not.
Comment 1 discoleo 2007-06-01 06:38:45 UTC
Well, I know from my own experience that date input is a pain in the ass. I
therefore support any modifications that enhance this.

> If date formatting was turned off by default, and you would have to choose
> a date format for the cell/range of cells, before date information is inputed,
> that would save some confusing I think.

Indeed, I would like to have the option to turn automatic date recognition off.
I often write both in Romanian, German and English, in various system locales,
so the input gets almost always messed up.

Even more troublesome is the date pattern, i.e. DD.MM.YY vs MM.DD.YY. I never
use the MM.DD.YY, but OOo insists on this (because I have installed the English
version, to avoid various other localisation problems).

I would welcome a general setting, where one could opt for a standard date
format, overriding system defaults and the like. Also, there should be an option
to set the output format, IF COPY/PASTE or exporting to CVS files (or other

In summary, the 'date' needs some real cleanup. Building some sensible
formatting options seems a good start.
Comment 2 discoleo 2007-11-16 15:51:54 UTC
discoleo -> mawe3361

You need NOT adjust the OOo version. This indicates, when the problem was first
reported. Therefore, you should set it back to OOO 2.2, because you first
reported it for OOo 2.2.

This problem still persists in OOo 2.3, but this can be mentioned using a short

What this issue discusses:

A.) BETTER DATE Handling
  1.) a way to disable auto-date recognition
  2.) a way to set the date format globally for Calc
     (or locally for a spreadsheet ?)

Comment 3 discoleo 2007-12-14 21:45:40 UTC
Recently, there was a discussion on the SC-mailing list regarding the auto
formatting of dates, see e.g.: and and
[SUBJECT: 'Date input']
Comment 4 kyoshida 2007-12-14 21:58:08 UTC
Let's at least confirm this issue.  The problem is real.
Comment 5 frank 2008-01-10 11:19:54 UTC
and re-assigned to requirements team
Comment 6 discoleo 2008-04-23 19:34:00 UTC
Just as a note, issue 88429 emphasizes this same problem, see:

Please vote for that issue (as it already has 7 votes, it might be relevant to
vote there).
Comment 7 discoleo 2008-04-23 19:43:52 UTC
I posted the *wrong issue* in my previous post. This is now corrected:

Issue 87999 emphasizes this same problem, see:

Please vote for that issue (as it already has 11 votes, it might be wise to
vote there).
Comment 8 mawe3661 2009-01-12 12:35:41 UTC
Any news? Do you need clarification of my suggestion?
Comment 9 Rainer Bielefeld 2009-11-27 10:43:41 UTC
*** Issue 106924 has been marked as a duplicate of this issue. ***
Comment 10 Rainer Bielefeld 2009-11-27 13:15:29 UTC
*** Issue 87557 has been marked as a duplicate of this issue. ***
Comment 11 f.a.b.i.o 2009-11-27 15:28:22 UTC
In issue 87557 was not wanted date autoconvert in this cases:

V1 --> 1.5.2001
1/3  --> 1.3.

... in czech localization of OOo

Comment 12 Rainer Bielefeld 2009-12-03 17:16:09 UTC
*** Issue 106336 has been marked as a duplicate of this issue. ***
Comment 13 f.a.b.i.o 2009-12-09 09:21:41 UTC
i found issue 33723 "Ability to turn off Number Recognition for incomplete dates
in Calc" - it is exactly that i want.

I move my votes to 33723. Please move your too if you have same opinion.