https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #43 from Eike Rathke <er...@redhat.com> --- I give up. Specify *ALL* those functions to truncate to return *correct* values (i.e. change only the definition of SECOND() to Second = INT(MinuteFraction * 60)) and be incompatible with Excel, or specify *ALL* functions to return the sick Excel rounded values for interoperability. But please do not introduce optional parameters to functions that are established for decades and would cause pain for all interoperability handling. (In reply to Regina Henschel from comment #40) > I do not see that. If the parameter is missing, the functions behave as > specified in ODF 1.3. Only the contradiction in ODF 1.3 is removed, that ODF > 1.3 on one hand specifies to round in the SECOND function resulting in range > 0 to 60, and on the other hand specifies a range of 0 to 59 for the SECOND > function. The other functions all truncate in ODF 1.3. So applications > having implemented ODF 1.3 should produce the same results as in ODF 1.3, > when they get an ODF 1.4 document without parameter. I think, that is a good > backward compatibility. While that is true in itself, at least two other major implementations, Excel and Gnumeric, don't implement the truncating ODF specification. And certainly Excel *never* would change its implementation just because ODF specified something different. So an interoperability problem already exists and for date+time 2023-12-31T23:59:59.6 their Excel behaviour is YEAR: 2024 MONTH: 1 DAY: 1 HOUR: 0 MINUTE: 0 SECOND: 0 Obviously wrong but if they insist.. > How would you then solve the request to have functions, that behave like > Excel? Probably best would be to define the existing functions to rounding Excel behaviour for interoperability, and introduce new truncating functions YEAR.CLOCK(), MONTH.CLOCK(), ... (or whatever term might be appropriate instead of CLOCK). Fwiw, Calligra Sheets does something very odd and somehow imports that fixed date+time value with the value of NOW() instead (and displays an empty cell, but in the input line displays the original value), but entered manually it truncates YEAR,MONTH,DAY (as specified by ODFF) and rounds HOUR,MINUTE,SECOND; so that's out of band anyway. -- You are receiving this mail because: You are the assignee for the bug.
