On Fri, Jan 10, 2014 at 11:12 AM, Jürgen Schmidt <[email protected]> wrote: > Hi, > > 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? >
OpenForumula (the spreadsheet formula part of ODF 1.2) says: ------ 6.10.5 DAY Summary: Returns the day from a date. Syntax: DAY( DateParam Date ) Returns: Number Constraints: None Semantics: Returns the day portion of a date. ------ So DAY returns a number. I hope we all agree that this number is 1, in your example, and cannot be anything else. Then the question is how we convert from numbers to dates. This is implementation-defined. Even totally within Excel it is inconsistent because Excel on the Mac uses a different base date (1/1/1904) than Excel on Windows. And as you point out OpenOffice uses a different reference date. So in practice the implicit conversion from numbers to dates is a non-portable construct. A smart spreadsheet application would warn the user whenever they tried to do this kind of conversion. The preferred technique is to use the DATE() function to explicitly convert from integers to a date value. This is portable. Regards, -Rob > Juergen > > > OpenOffice Help > ### > DAY > 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. > > Syntax > DAY(Number) > Number, as a time value, is a decimal, for which the day is to be returned. > > Examples > 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 > ### > DAY > 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. > > Syntax > > DAY(serial_number) > > 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. > > Remarks > > 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. > > Example > > The example may be easier to understand if you copy it to a blank worksheet. > ### > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
