On Fri, Jan 10, 2014 at 12:07 PM, 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 :-( >
Which is why we should warn them when they are doing something non-portable. It is like a compiler warns you if you do something dumb. User behavior is part of the problem here. For example, one solution might be to have a "safe mode" in AOO where implicit conversions like this don't exist. If you try, you get an error, just as if you tried to enter a formula like = "fish" + 42 Did you know that there is a group dedicated the studying mistakes like this that users make in spreadsheets, the European Spreadsheet Risks Interest Group: http://www.eusprig.org/horror-stories.htm It is a real problem. Users might complain, but when I read about spreadsheet mistakes costing millions of dollars I wonder if users are complaining about the right things? Stricter type checking in spreadsheets (if made optional) would be huge help. -Rob > 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. >>> ### >>> >>> --------------------------------------------------------------------- >>> 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] >> > > > --------------------------------------------------------------------- > 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]
