https://bz.apache.org/ooo/show_bug.cgi?id=119374
--- Comment #6 from orcmid <[email protected]> --- My vote is to do this the Excel 2013 way (and the LibreOffice 5 way for negative results). One could argue that Andreas misunderstands the ODF 1.2 specification, but it doesn't matter. - - - - - - - - - - - - I remember what this is about. For ODF 1.2 Part 2 OpenFormula, note the definitions in 4.3.2 Time "Time is a subtype of Number "Time is represented as a fraction of a day" and 4.3.3 Date, "Date is a subtype of Number. "Date is represented by an integer value." "A serial date is the expression of a date as the number of days elapsed from a start date called the epoch." "Evaluators SHALL support all dates from 1904-01-01 through 9999-12-31 (inclusive) in calculations, SHOULD support dates from 1899-12-30 through 9999-12-31 (inclusive) and MAY support a wider range. There are important notes about not assuming a particular epoch value, dealing with whether or not 1900 is a leap year or not, and that negative values MAY be supported to deal with dates before the epoch. and 4.3.4 DateTime "DateTime is a subtype of Number. It is a Date plus Time." IMPORTANT: For the above to work as a calculation, rather than how to understand the number, Time must not be negative. That would end up being in the day before the Date-expressed date. So, TIME returning a value only in the range 0 <= time < 1 is admissible. There may be bugs, and it may be a function of weird parameter cases, but the specification in 6.10.17 states that the result is a Time value (see 4.3.2). HERE'S THE PROBLEM The OpenFormula definition is MEANT to be compatible with Excel's definition, at least for the ranges that Excel accepts. Note that 6.10.17 is expected to produce a Time value, not a DateTime value, and that negative DateTime values are not required to be supported. Under that interpretation it is a mistake to assume that 60*(hours*60+minutes)+seconds is the answer (that would likely be a DateTime), it is the Time portion (the fraction of the day) consumed by the result. Treatment of negative results from that formula is not covered by the ODF 1.2 specification at all. ABOUT EXCEL COMPATIBILITY However, Excel does indeed deliver a DayTime result, just as Andreas asserts. Excel does not allow negative DayTime values, and allowing them in Calc will lead to different results. Furthermore, our cousins at LibreOffice have "corrected" Calc to satisfy the sample test file. If it were up to me, I would say interoperability in reality is more important than strict reading of the specification. I haven't checked to see if there are any corrections to be made to TIME() in ODF 1.2 (or ODF 1.3). Although there are sound principles on TIME() producing a clean Time value, that is not the reality and interoperability in practice is far more important (I say). -- You are receiving this mail because: You are the assignee for the issue.
