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. >> ### >> >> --------------------------------------------------------------------- >> 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]
