Issue 83667 - Auto-format erroneously converting TEXT value into DATE
Summary: Auto-format erroneously converting TEXT value into DATE
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOo 2.3
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2007-11-15 15:08 UTC by rjms
Modified: 2013-08-07 15:12 UTC (History)
2 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 rjms 2007-11-15 15:08:24 UTC
When copyijng data from the USDA database
( into CALC, there is a
problem where CALC insists on auto-formatting the input and it causes an error.

Open this link ( and
choose any food.

Now highlight the data, and copy it into CALC.  Everything works fine except in
the "LIPIDS" identifiers, in the first column.  Sample column from Bananas:

Fatty acids, total saturated 	

Once that data is in the fields, it gets converted into a "time" value so that
4:0 becomes 04:00:00 AM.  No attempt to change the data into 4:0 by defining the
data as text data works, and even if the whole column is defined as text data,
CALC still insists on auto-formatting 4:0 and similar values into time values. 
The manual work around is to enter '4:0.  Tis is a major problem if large
anounts of data are being studied.  It would be better if CALC re-displayed the
data as it was pasted in once the auto-formatted time value was switched back to
text.  A better solution would be:
1)User selects column to be used for data.
2)User selects column and the selects "Format Cells"
3)User selects "text"
4)All data pasted into a column is displayed verbatim,with no conversions.
Comment 1 rjms 2007-11-15 15:22:10 UTC
Turning auto-correct OFF when pasting the text data does not fix this problem.
Comment 2 frank 2007-11-16 11:18:02 UTC

as we work in the same way Excel does, this Issue is not a defect. The value 4:3
is in most locales a time value, therefore on paste the default time forrmat for
the choosen locale is used to display these values.

The enhancement in this case is the possibility to choose if the HTML Values
should be pasted as text or as values according to the expected formatting as it
is now.

Comment 3 jbf.faure 2008-05-10 21:56:24 UTC
We have a similar issue when importing the data from the following url
in Calc with locale FR(France).
In this file numbers are formated with belgium conventions : dot for thousand 
separator and comma for decimal separator. When importing this data with 
linguistic environment configuring as FR(France), some numbers are converted 
in dates.

It should be useful to have some option in import function allowing to 
indicate to Calc what are thousand and decimal separators for imported data

Another improvement may be to remove automatic date formatting in such import