Issue 103295 - Date conversion Error while pasting dates in MM/DD/YYYY format
Summary: Date conversion Error while pasting dates in MM/DD/YYYY format
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOO310m11
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2009-07-02 20:09 UTC by viren297
Modified: 2013-01-29 21:50 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Excel 2003 sheet with Date Example (7.50 KB, application/
2009-07-04 23:10 UTC, tomwb
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description viren297 2009-07-02 20:09:32 UTC
While pasting dates in the DD/MM/YYYY format into a new calc spreadsheet, the
dates in the first 12 rows get automatically converted to MM/DD/YY, but the from
13th row onwards, neither the dates convert automatically nor do they convert
even on applying specific formatting on them:

Sample Inputs dates:


Erroneous Output:
Comment 1 wope 2009-07-03 02:05:03 UTC
I can not confirm this, wether on Win XP nor on Linux
Have you formated the cells A13 to A16 ? 
set Prio to P3, see
Comment 2 wope 2009-07-03 02:09:39 UTC
set  to invalid
Comment 3 viren297 2009-07-03 15:14:49 UTC
if the intention of closing the issue is to show off that it was 'resolved' in a
record time, then I have no problem with that. But then do not expect that
OpenOffice will ever be treated a good quality product, and users like me will
be discouraged to further report (and help) issues !!

I already mentioned, that I even tried to apply formatting of different kind and
there wasn't any effect of that on the row no. 13 and below.
Comment 4 tomwb 2009-07-03 20:36:53 UTC
I can confirm the effect, but it may not be a defect.  It is caused on my system
by the English(US)language or locale setting.  Anything greater than 12 in the
month slot is not recognized as a date.  Therefore that data with a month
greater than 12 is pasted as text.  If one changes the locale to English(UK)
(DD/MM/YY), the pasted data is correctly recognized as a date.

OOO310m11 & OOO2.4.1 on Vista SP2
Comment 5 viren297 2009-07-04 19:40:03 UTC
I would still consider it a defect, since OOO is incorrectly assuming it to be a
month in first place, than a date. 
If the locale is set to EN(US), it shouldn't be that one has to paste the dates
always in MM/DD/YYYY format.
Secondly, even if OOO assumes that the date is in MM/DD/YYYY format, it should
at least allow the formatting to be change when someone manually attempts to
change it. Even that isn't happening here.
Third, if locale was to control that you put the date always in one particular
format, then how has MS Excel or Sun's Soffice got it right ? This problem
doesn't occur in soffice or MS Excel.
Comment 6 tomwb 2009-07-04 23:09:44 UTC
I just created a Excel workbook that shows the same handling of the date,
whether copy/paste or typed in.
Will attached Excel file.
000310m11 on Vista SP2 and Excel 2003 on XP SP2.
Comment 7 tomwb 2009-07-04 23:10:52 UTC
Created attachment 63364 [details]
Excel 2003 sheet with Date Example
Comment 8 wope 2009-07-05 00:23:49 UTC
I only testet it, with the german localisation.
It may be a duplicate to issue 103213.
If it is, please send me a message
Comment 9 jbf.faure 2009-07-05 08:41:34 UTC
For me it's not a defect. As far as I know,  the formatting of the cell does not
decide whether a string that seems to be a date must be interpreted as a date.

@viren297 : in which case do you encounter this problem ? When you copy/paste
from the web ?
I think you have a workaround : paste your text to a plain text document in a
text editor, copy the result and paste it in your speadsheet. Now Calc open an
import dialog which allows you to choose the date format of your data.
I tested this procedure under Ubuntu with gedit as text editor.
Comment 10 viren297 2009-07-06 18:35:10 UTC
@tomwb: I have tested it in Excel 2007, and this problem does surface. If you
require I can attach the sheet. And it doesn't occur in Soffice either.
@wope: I am not sure if issue 103213 and this, are one and the same.
@jbfaure: I have tried all, i.e. writing into spreadsheet, copying from a text
editor, and pasting in html format etc. etc. All show the same results.
As far as using a workaround is concerned, well, people need a work around, when
there is an issue / defect / problem with the usual functioning of the
application, which again confirms that this indeed is a defect.
Also, as I said earlier, leave alone auto-formatting not sensing the date format
correctly, its also about manual formatting not working either !!
Do you still prefer not to consider it a defect ? If so, please go ahead and
mark it as "Not A Defect" and feel happy about it. But, I must say that, focus
is more here on "closing" the issue than "resolving" it, and this way products
don't improve.