On 10.01.2014 17:12, Jürgen Schmidt wrote:
I received a xls document with some date values, date related functions
and formatting and noticed an interesting interoperability problem.
For example:
Cell Value Format Visible Value
A1 01/01/2014 MM/DD/YY 01/01/2014
A2 =DAY(A1) DD 31
Excel shows the value "1" and the user expected the same value in
OpenOffice but we show 31.
The reason can be explained by looking in the help of the DAY function
in Excel and OpenOffice (see below)
The problem is the different reference date and counting. The serial
number 1 in Excel is 01/01/1900. In OpenOffice we count from 0 and
serial number 1 in AOO is related to 12/31/1899 because the reference
date in AOO is 12/30/1899.
FWIW the rationale behind the different reference date is given in [1].
Apparently the problem solved by using a different reference was that
the year 1900 was not a leap year, because of the modulo-100 rule.
[1] https://issues.apache.org/ooo/show_bug.cgi?id=26125#c2
If cell A2 would be formatted as number everything would be fine. But
formatted as date it takes the integer value 1 as offset to our
reference date, means 12/30/1899 + 1 day = 12/31/1899 = 31.
Weighing the benefits of the workaround mentioned above with
interoperability problems or the problem Jürgen just described, my scale
would tip for a reference date (== day 0) of Dec 31, 1899.
So this means it is wrong or better misleading by design. I am not sure
if this can be fixed or should be fixed.
Any opinions?
If there is a way to change our reference day without losing backwards
compatibility for existing documents then we should go for it.
Herbert
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
