https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #8 from Albrecht Müller <albrecht.muel...@astrail.de> --- I think the problem is definitely not fixed yet as - without stating this explicitly - the specification now uses two different conventions that contradict each other. There is a truncating convention where the transition from one day to the next occurs exactly at midnight. This convention is used for example in 4.11.7 Basis (https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017922_715980110) The other convention is the rounding convention where this transition occurs half a second earlier. The proposed definitions of the YEAR, MONTH, DAY, HOUR, MINUTE and SECOND functions use that convention. So there is half a second each day where the conventions disagree to which day this time belongs. The bad thing is that the serial numbers that specify dates represent points in time that are exactly at boundaries of these disagreement regions. There may be use cases where the serial numbers are the results of some calculations that include round off errors, e.g. because they use time values that don't have exact representations. Due to these round off errors up to 50% of the results may fall into the region where the conventions disagree. Users may not be aware that they mix functions that use different conventions. Therefore they may see results that seem to be clearly wrong at random in up to 50% of the cases. Actually they observe a behaviour that follows from the specification. There is nothing an implementer of the functions can do about. The user has to use clumsy workarounds to get the expected results. I tried to find out how OOXML (https://www.ecma-international.org/publications-and-standards/standards/ecma-376/) handles this rounding problem. As I was not not able to find relevant rules I think the OOXML specification left this point unspecified. This way it does not prescribe a behaviour that users may consider buggy. -- You are receiving this mail because: You are the assignee for the bug.
