openoffice-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From J├╝rgen Schmidt <>
Subject Interesting interoperability problem with formatting of date
Date Fri, 10 Jan 2014 16:12:57 GMT

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.

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.

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?


OpenOffice Help
Returns the day of given date value. The day is returned as an integer
between 1 and 31. You can also enter a negative date/time value.

Number, as a time value, is a decimal, for which the day is to be returned.

DAY(1) returns 31 (since OpenOffice starts counting at zero from
December 30, 1899)
DAY(NOW()) returns the current day.
=DAY(C4) returns 5 if you enter 1901-08-05 in cell C4 (the date value
might get formatted differently after you press Enter).

Excel Help
Show AllShow All

Returns the day of a date, represented by a serial number. The day is
given as an integer ranging from 1 to 31.



Serial_number     is the date of the day you are trying to find. Dates
should be entered by using the DATE function, or as results of other
formulas or functions. For example, use DATE(2008,5,23) for the 23rd day
of May, 2008. Problems can occur if dates are entered as text.


Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1,
and January 1, 2008 is serial number 39448 because it is 39,448 days
after January 1, 1900. Microsoft Excel for the Macintosh uses a
different date system as its default.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian
values regardless of the display format for the supplied date value. For
example, if the display format of the supplied date is Hijri, the
returned values for the YEAR, MONTH and DAY functions will be values
associated with the equivalent Gregorian date.


The example may be easier to understand if you copy it to a blank worksheet.

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message