https://bugs.documentfoundation.org/show_bug.cgi?id=173740
Bug ID: 173740
Summary: Calc: Add an optional DATEDIF parameter for corrected
date-difference semantics while preserving legacy
compatibility
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: enhancement
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
## Enhancement request
Add an optional fourth parameter to DATEDIF() allowing users to explicitly
request corrected date-difference semantics, while leaving the existing
three-argument behaviour completely unchanged for backward compatibility and
interoperability.
### Motivation
This enhancement is motivated by bug 171969 and the discussion there.
DATEDIF() has several known historical edge cases. In particular, the `"md"`
interval can return negative, zero or otherwise unexpected results for some
end-of-month combinations.
For example:
=DATEDIF(DATE(2024;1;31);DATE(2024;3;1);"md")
currently returns:
-1
The existing behaviour has deliberately been kept because changing it would
cause old spreadsheets to produce different results after upgrading
LibreOffice.
I agree that existing formulas must not silently change their results.
However, changing the behaviour of existing formulas is not necessary in order
to provide users with a corrected calculation.
### Proposal
Extend DATEDIF() with an optional fourth parameter, conceptually:
DATEDIF(StartDate; EndDate; Interval [; Mode])
For example:
Mode omitted (default) = current legacy / compatibility behaviour
Corrected mode = corrected date-difference behaviour
The exact parameter name and type (Boolean, numeric mode, etc.) can of course
be decided by the developers.
Conceptually, something such as:
=DATEDIF(A1;B1;"md")
would continue to behave exactly as it does today.
Existing spreadsheets would therefore remain completely unaffected.
A user who explicitly wants corrected semantics could use something such as:
=DATEDIF(A1;B1;"md";1)
and obtain the corrected result.
For the example above:
=DATEDIF(DATE(2024;1;31);DATE(2024;3;1);"md";1)
the corrected calculation would return:
1
because one complete month takes the date from 31-Jan-2024 to 29-Feb-2024,
leaving one day until 1-Mar-2024.
### Backward compatibility
The important point of this proposal is that it does not change the semantics
of any existing DATEDIF formula.
A document containing:
=DATEDIF(A1;B1;"md")
would return exactly the same result before and after this enhancement.
Therefore an old spreadsheet opened in a newer LibreOffice version would not
unexpectedly recalculate to different values.
Only formulas whose authors explicitly use the new optional parameter would use
the new semantics.
This separates two requirements which do not necessarily need to conflict:
* preserving historical DATEDIF behaviour for compatibility;
* allowing Calc users to explicitly request a reliable/corrected calculation.
### OpenFormula compatibility
OpenFormula specifies DATEDIF with three arguments, but the OpenFormula
specification explicitly permits evaluators to extend functions by accepting
additional optional parameters.
OpenDocument 1.3 Part 4, section 2.3.1 states that an evaluator may implement:
"additional optional parameters for functions"
and section 6.2 also states:
"Evaluators may extend functions by permitting fewer or additional
parameters, which documents may use. Extended functions may result in a lack of
interoperability."
The specification also requires such extensions to be clearly documented.
Therefore an optional fourth parameter is explicitly within the extension
mechanism contemplated by OpenFormula.
The standard three-argument syntax would remain unchanged and interoperable.
Only a user voluntarily choosing the LibreOffice extension would potentially
lose formula interoperability with applications that do not implement that
fourth parameter.
### Interoperability
Lack of immediate support for the optional parameter in other spreadsheet
applications should not by itself prevent Calc from introducing a useful
extension, provided that:
* the existing three-argument syntax and behaviour remain unchanged;
* use of the extension is explicit;
* the extension is properly documented;
* export filters handle the unsupported extension appropriately.
LibreOffice already implements functions and extensions that are not
necessarily available with identical semantics in every spreadsheet
application.
Someone has to be the first implementation of a useful extension.
### Why extend DATEDIF instead of requiring alternative formulas?
DATEDIF provides a concise and very useful abstraction for date differences.
Without it, equivalent calculations can become considerably more complicated
and less readable.
For example, total days can easily be calculated as:
=B2-A2
but calculating complete years without DATEDIF may require an expression such
as:
=YEAR(B2)-YEAR(A2)-IF(EDATE(A2;12*(YEAR(B2)-YEAR(A2)))>B2;1;0)
and complete months can require an even more complex expression.
Compare those with:
=DATEDIF(A2;B2;"y")
=DATEDIF(A2;B2;"m")
The problem is therefore not that the operation provided by DATEDIF is obsolete
or useless. The interface remains concise and useful; only some historical
calculation semantics have known shortcomings.
### Scope
This enhancement does not request any change to the existing three-argument
DATEDIF behaviour discussed in bug 171969.
It requests an explicit opt-in calculation mode so that:
old formula -> old result
new opt-in formula -> corrected result
This would preserve backward compatibility while allowing Calc to provide
improved date-difference semantics for users who explicitly request them.
--
You are receiving this mail because:
You are the assignee for the bug.
