https://bugs.documentfoundation.org/show_bug.cgi?id=171969
[email protected] changed: What |Removed |Added ---------------------------------------------------------------------------- Ever confirmed|0 |1 Resolution|NOTABUG |--- Status|RESOLVED |REOPENED --- Comment #2 from [email protected] --- I think this issue deserves reconsideration rather than RESOLVED NOTABUG. There is an important distinction between reproducing Excel behavior for interoperability and the semantics of Calc's ODF/OpenFormula DATEDIF function. The current implementation is in: sc/source/core/tool/interpr2.cxx ScInterpreter::ScGetDateDif() The function itself is introduced in the source as: // fdo#44456 function DATEDIF as defined in ODF1.2 (Par. 6.10.3) However, the implementation of the "md" interval explicitly says: // Algorithm's roll-over behavior extracted from Excel by trial and // error... and deliberately constructs possibly non-existent dates and then calls: aDate1.Normalize(); nd = aDate2 - aDate1; This explains the erroneous negative results. For example: =DATEDIF(DATE(2023;1;31);DATE(2023;3;1);"md") returns -2. Internally, the calculation effectively creates 31-Feb-2023, which Normalize() rolls forward to 3-Mar-2023. The subtraction then becomes: 1-Mar-2023 - 3-Mar-2023 = -2 Similarly: =DATEDIF(DATE(2024;1;31);DATE(2024;3;1);"md") returns -1 because 31-Feb-2024 is normalized to 2-Mar-2024. This is therefore not related specifically to leap years. Microsoft itself documents "MD" as a known issue and states that it may return a negative number, zero, or an inaccurate result. Therefore Calc is currently deliberately reproducing a behavior that Microsoft itself documents as unreliable. LibreOffice Help, on the other hand, describes "md" as: Number of whole days when subtracting years and months from the difference of Start date and End date. A negative number of remaining whole days for StartDate < EndDate is difficult to reconcile with that description. A possible correct end-of-month handling would be to clamp the requested day to the last valid day of the preceding month instead of allowing Date::Normalize() to roll it into the following month. For example: 31-Feb-2023 -> 28-Feb-2023 31-Feb-2024 -> 29-Feb-2024 which would make the above examples return 1 rather than -2 or -1. There is also an interoperability question here. If exact Excel behavior must be preserved for imported/exported OOXML files, perhaps the Excel-compatible behavior should be separated from the ODF/OpenFormula behavior instead of making the ODF DATEDIF implementation reproduce an acknowledged Excel defect. Calc already distinguishes ODF and Excel semantics for other functions. For example, the same source file explicitly distinguishes ODFF CEILING from Excel CEILING.MATH because their semantics differ. Therefore I suggest reconsidering this issue as one of: 1. Fixing the ODF/OpenFormula DATEDIF "md" implementation while preserving Excel-compatible behavior separately where needed; or 2. If the current behavior is intentionally required, documenting explicitly in LibreOffice Help that "md" deliberately reproduces Excel's known erroneous behavior and may return negative or inaccurate results. Related issue: bug 172789 discusses another DATEDIF end-of-month problem, this time with interval "m". -- You are receiving this mail because: You are the assignee for the bug.
