My assumption without an accompanying attached document is that the 
dates entered are in 'short date' format i.e. as determined by the 
regional settings. This is the default Excel date format. If you 
select 'format cells->Number->Date', the two '*' before the date 
formats determine the system short and long date formats. If you use 
the short date format, then Excel simply stores this fact and not the 
actual format i.e. MM/DD/YYYY. The corresponding short date format in 
Calc is MM/DD/YY. Remember we have to be able to cope with 
localisation issues. This same 'short date' format will appear as 
DD/MM/YY or DD.MM.YY in other locals and your short date format may 
be different on someone's else's machine. 

So to ensure compatability across applications and machines, you 
could have explicitly chosen to set your date format to MM/DD/YYYY in 
format cells ->Number->Date->Type. 

If this assumption is incorrect, please attach a sample doc. Thanks.

Created attachment 6484 [details]
Test Case

The attached doc confirms that the dates are in short-date format. 
The problem is that Excel file does not contain the format m/d/yyyy 
as you suggest but rather it stores the format as an index to a built-
in table. We don't know what the format of the original doc is in. 
This explains why Excel will display the same date differently on two 
machines with different short-date format settings. 

Calc simply uses the short date m/d/yy for the US locale. Thus to 
ensure compatability across machines and across applications, you 
should employ the workaround and explicity set the date format as 
suggested earlier. This workaround would also ensure consistency in 
the round trip of Excel to Calc to Excel. 

I would add that one improvement we should do would be to ensure that 
instead of exporting the built-in format to a user-defined format, we 
could export it as the original built-in index number. I would 
willingly confirm that as an issue and use this issue as a 
placeholder for such a change.

I should have clarified, the original document in the test case was 
done in Excel 97.  I didn't have Excel 2002 available at the time, 
but it exhibits the same behavior.  

I don't think I was explaining myself well at first, but your 
suggestion for improvement in export seems to be right on. That
would at least prevent it from changing on the Excel side while 
allowing OpenOffice to handle it in any manner.

confirming request to export Excel built-in formats rather than user-
defined formats

assuming ownership + target

reset to started

I encounter the same/similar problem when simply entering a date in
mm/dd/yyyy format, but using the default cell format (in USA), which
is mm/dd/yy. The full 4-digit year is still in the data, but is
displayed as a 2-digit year. What I would like Calc's behavior to be
is, if a year is entered as four digits (e.g. mm/dd/yyyy) that the
default format for that locale be selected as the one with a 4-digit
year, rather than the one with a 2-digit year. If the user explicitly
selects a date format, then of course that should be followed, but if
the format is allowed to default, it should show the full four digits
of the year if that is what was entered.

I want to try and clarify this. This issue is solely concerned with
the import and export of date formats from Excel documents and not how
Calc formats dates as they are entered by a user.

OK, then I will open a new issue

Marking this as fixed. I have recently checked in some code to fix
issue 13557 and an internal bug to address some CJK and date/time
formatting issues to xistyle.cxx. The result is that we now default
Excel docs containing the Excel short date to using YYYY rather than
YY as this is the windows default short date format. This fix is now
in CWS/calc16.  

set to verified

closed verified

closed accidentially, so reopening

Frank

restoring status as fixed.

Frank

*** Issue 18029 has been marked as a duplicate of this issue. ***

*** Issue 18029 has been marked as a duplicate of this issue. ***

closed.