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? 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]
