On Fri, Jan 10, 2014 at 12:25 PM, Rory O'Farrell <[email protected]> wrote: > 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?) >
The problem is the average user won't know when to enable this option. If you're sent a XLS file you don't know if it was created in Excel or OpenOffice. Same for an ODS file. In fact your needs might vary from file to file. If there was a way to detect what app created the spreadsheet, then you might be able to do something. But if you have a situation where two people were collaborating, and both making edits, with different applications, or even with Excel on Windows and Excel Mac, then all bets are off. We spent many hours debating this at OASIS when developing OpenFormula. There were no good solutions, just a number of bad solutions to pick from. That's why I consider the implicit conversion from numbers to dates to be bad, something that the author of a spreadsheet should avoid and be warned about or even prevented from doing. Of course, the innocent receiver of a spreadsheet just wants it to work. But there are limits of what we can do 20 years later to fix past design mistakes of 1st generation spreadsheet applications. -Rob > -- > Rory O'Farrell <[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]
