On Fri, 10 Jan 2014 18:07:10 +0100 Jürgen Schmidt <[email protected]> wrote:
> On 1/10/14 5:39 PM, Rob Weir wrote: > > 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. > > the problem is that most users probably don't care about this from their > end users perspective and will simply complain :-( > > Juergen > > > > > > 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. > >> ### > >> > >> --------------------------------------------------------------------- Insert an Option in /Tools /Options : OpenOffice Calc : Calculate page, under Dates: a checkbox for dates compatible with Excel (and some warning about the error?) -- Rory O'Farrell <[email protected]> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
