xref: /trunk/audit/openformula-function-gaps/datedif-semantic-design.md (revision 195598447694ec8542914a9b51ae9942e8ebacd9)
1<!--
2 Licensed to the Apache Software Foundation (ASF) under one
3 or more contributor license agreements.  See the NOTICE file
4 distributed with this work for additional information
5 regarding copyright ownership.  The ASF licenses this file
6 to you under the Apache License, Version 2.0 (the
7 "License"); you may not use this file except in compliance
8 with the License.  You may obtain a copy of the License at
9
10   http://www.apache.org/licenses/LICENSE-2.0
11
12 Unless required by applicable law or agreed to in writing,
13 software distributed under the License is distributed on an
14 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 KIND, either express or implied.  See the License for the
16 specific language governing permissions and limitations
17 under the License.
18-->
19
20DATEDIF Semantic Design — ODF 1.4 §6.10.3
21==========================================
22
23Branch: openformula
24HEAD: 0224e3dc6c (H.1/H.2 complete)
25
26Updated 2026-11-18 to match the code after the post-merge review. Jim chose
27Excel-compatible behavior for the cases ODF leaves open; decisions 3, 4 and 8
28and the "md"/"yd" algorithms below were changed to what the code does now.
29The section "2026-11-18: as implemented" at the end is the current reference.
30
31This document specifies the semantics for the DATEDIF function implementation
32in Apache OpenOffice Calc, conforming to ODF 1.4 Part 4, Section 6.10.3.
33
34Normative Reference
35-------------------
36
37ODF 1.4 OpenFormula, Section 6.10.3 — DATEDIF: Difference between two dates.
38
39Function Signature
40------------------
41
42DATEDIF( StartDate ; EndDate ; Format )
43
44- StartDate: Date — the start of the interval.
45- EndDate: Date — the end of the interval.
46- Format: Text — one of "y", "m", "d", "md", "ym", "yd".
47
48Result: Number — the difference between the two dates in the units specified
49by Format.
50
51ODF 1.4 §6.10.3 Defined Formats
52-------------------------------
53
54| Format | Meaning                    |
55|--------|----------------------------|
56| y      | Number of complete years   |
57| m      | Number of complete months  |
58| d      | Number of days             |
59| md     | Days ignoring months/years |
60| ym     | Months ignoring years      |
61| yd     | Days ignoring years        |
62
63Semantic Decisions
64------------------
65
661. Start date earlier than end date: Normal forward interval. Compute the
67   difference normally for all formats.
68
692. Start date equal to end date: Zero-length interval. All formats return 0.
70
713. Start date later than end date (reverse interval): The ODF specification
72   does not define this case. Excel 16 returns #NUM! for every format
73   (checked). Policy (2026-11-18): an error (Err:502), as Excel. The earlier
74   policy of negating "y", "m", "d" and returning absolute components was
75   dropped.
76
774. End-of-month dates: When computing complete months ("m"), a month is
78   complete only when the end date's day is >= the start date's day within the
79   same month context. If the end date's day is less than the start date's day
80   (e.g., Jan 31 to Feb 28), the last partial month is not counted. Policy
81   (2026-11-18): a month is complete only when the end day is >= the start
82   day. ODF 1.2 and 1.4 say "If there is not a complete month between the
83   dates, 0 will be returned", and Excel gives 0 for Jan 31 to Feb 29, 2024.
84   The end-of-month substitution was dropped.
85
865. February 28/29 and leap years: Leap-day anniversaries are handled by
87   comparing month/day pairs. Policy: compare month/day pairs directly, so
88   Feb 29, 2024 to Feb 28, 2025 is 0 complete years (Excel also gives 0).
89
906. Fractional time components: Date serials may contain fractional parts
91   representing time of day. DATEDIF operates on calendar dates by discarding
92   time fractions (floor to midnight). Time components do not affect the
93   result. Policy: Floor both dates to integer serials before computation.
94
957. Null date: The document's configured null date is used via
96   pFormatter->GetNullDate(). No assumption is made about the serial epoch.
97
988. Invalid format strings: Any format string other than "y", "m", "d", "md",
99   "ym", "yd" produces an error result. Upper and lower case are equivalent
100   (2026-11-18), as in Excel; ODF does not say.
101
1029. Missing arguments / invalid argument types: Fewer than 3 arguments, or
103   non-date arguments, produce an error result.
104
10510. Date serials outside supported range: Serials that produce invalid Date
106    objects produce an error result.
107
108Algorithm Specification
109-----------------------
110
111```text
112Helper: date_from_serial(serial) -> Date
113  null = pFormatter->GetNullDate()
114  return null + floor(serial)
115
116Helper: is_last_day_of_month(date) -> bool
117  return date.GetDay() == date.GetDaysInMonth()
118
119Format "d" — Calendar day difference:
120  d1 = date_from_serial(StartDate)
121  d2 = date_from_serial(EndDate)
122  return (d2 - d1) in days (serial difference, floored)
123  For reverse: compute forward, negate.
124
125Format "m" — Complete calendar months:
126  d1 = date_from_serial(StartDate)
127  d2 = date_from_serial(EndDate)
128  months = (d2.GetYear() - d1.GetYear()) * 12 + (d2.GetMonth() - d1.GetMonth())
129  if d2.GetDay() < d1.GetDay():
130    months -= 1
131  elif d2.GetDay() >= d1.GetDay() and is_last_day_of_month(d1) and is_last_day_of_month(d2):
132    # both end-of-month: count the full month
133    pass
134  # clamp to non-negative for forward intervals
135  For reverse: compute forward, negate.
136
137Format "y" — Complete calendar years:
138  d1 = date_from_serial(StartDate)
139  d2 = date_from_serial(EndDate)
140  years = d2.GetYear() - d1.GetYear()
141  if (d2.GetMonth(), d2.GetDay()) < (d1.GetMonth(), d1.GetDay()):
142    years -= 1
143  # Feb 29 anniversary in non-leap year: month/day (2,28) < (2,29) handled
144  # naturally by tuple comparison above
145  For reverse: compute forward, negate.
146
147The algorithms below are the original design. Where they differ from the
148code, the code (see "2026-11-18: as implemented") wins; "md" and "yd" and the
149reverse-interval handling differ.
150
151Format "md" — Days ignoring months/years:
152  d1 = date_from_serial(StartDate)
153  d2 = date_from_serial(EndDate)
154  # Compare day-of-month, adjusting for month boundaries
155  day1 = d1.GetDay()
156  day2 = d2.GetDay()
157  # If end day < start day, borrow from previous month
158  if day2 < day1:
159    # Compute the last day of the month before d2's month
160    prev_month = d2.GetMonth() - 1
161    prev_year = d2.GetYear()
162    if prev_month < 1:
163      prev_month = 12
164      prev_year -= 1
165    Date prev_last(prev_month's last day, prev_month, prev_year)
166    day_diff = day2 + prev_last.GetDaysInMonth() - day1
167  else:
168    day_diff = day2 - day1
169  return abs(day_diff) for reverse-date compatibility
170
171Format "ym" — Months ignoring years:
172  d1 = date_from_serial(StartDate)
173  d2 = date_from_serial(EndDate)
174  months = d2.GetMonth() - d1.GetMonth()
175  # If end month < start month, this is a partial year
176  # The month component ignoring years: if we've passed the start month
177  # in the end year, count it; otherwise count from previous year
178  if months < 0:
179    months += 12
180  return abs(months) for reverse-date compatibility
181
182Format "yd" — Days ignoring years:
183  d1 = date_from_serial(StartDate)
184  d2 = date_from_serial(EndDate)
185  # Compute day-of-year difference, wrapping at year boundary
186  doy1 = d1.GetDayOfYear()
187  doy2 = d2.GetDayOfYear()
188  day_diff = doy2 - doy1
189  return abs(day_diff) for reverse-date compatibility
190```
191
192Implementation Notes
193--------------------
194
195- Use ::rtl::math::approxFloor() for flooring serials (consistent with
196  ScGetDiffDate360 in interpr2.cxx).
197- Use pFormatter->GetNullDate() for the document's null date.
198- Use Date class methods: GetDay(), GetMonth(), GetYear(), GetDaysInMonth(),
199  IsLeapYear(), GetDayOfYear(), operator+=, comparison operators.
200- Do not use fabs() to conceal negative results; handle sign explicitly.
201- Do not approximate months as 30 days or years as 365 days; use calendar
202  arithmetic.
203- Validate exactly 3 arguments via MustHaveParamCount.
204- (Superseded 2026-11-18: the format is case-insensitive.)
205
206Error Handling
207--------------
208
209- Wrong argument count: error via MustHaveParamCount.
210- Non-date arguments: error via GetDouble() validation + PushIllegalArgument.
211- Invalid format string: PushIllegalArgument().
212- Reverse dates (start > end): Err:502 (2026-11-18; Excel gives #NUM!).
213
214Files to Modify
215---------------
216
2171. main/formula/inc/formula/compiler.hrc — Add SC_OPCODE_DATEDIF = 415
2182. main/formula/inc/formula/opcode.hxx — Add ocDateDif = SC_OPCODE_DATEDIF
2193. main/formula/source/core/resource/core_resource.src — Register "DATEDIF" name
2204. main/sc/source/core/tool/interpr2.cxx — Add ScInterpreter::ScDateDif()
2215. main/sc/source/core/tool/interpr4.cxx — Add dispatch case ocDateDif
2226. main/sc/source/ui/src/scfuncs.src — Function Wizard entry
2237. main/sc/inc/helpids.h — Add HID_FUNC_DATEDIF
2248. main/sc/util/hidother.src — Register Help ID
2259. test/odf-validate/calcval-cases14.tsv — Regression tests
22610. audit/openformula-function-gaps/ — Update audit records
227
228H.3.1 Implementation/Review Notes (2026-11-17)
229----------------------------------------------
230
231Implementation: `ScInterpreter::ScDateDif()` in
232`main/sc/source/core/tool/interpr2.cxx`, declared in
233`main/sc/source/core/inc/interpre.hxx`, dispatched from
234`main/sc/source/core/tool/interpr4.cxx` (`ocDateDif`), with resources/help IDs
235registered and BIFF5/7 import mapped import-only (`EXC_FUNCFLAG_IMPORTONLY`) in
236`main/sc/source/filter/excel/xlformula.cxx` (ID 351 → `ocDateDif`).
237
238Semantics as implemented (ODF 1.4 §6.10.3):
239
240- Exactly 3 arguments: StartDate, EndDate, Format (popped Format, EndDate,
241  StartDate).
242- Date serials floored to calendar dates via `::rtl::math::approxFloor()`; time
243  fractions discarded. Calendar conversion uses `pFormatter->GetNullDate()`.
244- Invalid/unsupported date serials (`Date::IsValid() == false`) → illegal
245  argument error.
246- Format is case-sensitive: only `y`, `m`, `d`, `md`, `ym`, `yd` accepted;
247  any other value (including `Y/M/D` and empty string) → illegal argument error.
248- Forward intervals (`StartDate <= EndDate`): `d` = calendar day difference;
249  `y` = complete calendar years; `m` = complete calendar months with
250  end-of-month substitution when the start day is the last day of its month;
251  `ym` = `m - y*12` (months remaining after complete years);
252  `md` = day difference ignoring months/years, borrowing from the start date's
253  month when end day < start day;
254  `yd` = days from the anniversary (start date shifted forward by complete
255  years, adjusted for Feb 29 in non-leap anniversary years) to end date.
256- Reverse intervals (`StartDate > EndDate`): computed on the swapped forward
257  interval. `y`, `m`, `d` return the negated forward result; `md`, `ym`, `yd`
258  return a non-negative component value (Excel-compatible policy; ODF 1.4 does
259  not define reverse intervals). This is an implementation constraint/policy,
260  not a normative ODF requirement.
261
262Validation notes:
263
264- Source-level review/static verification only in this environment; Calc and
265  calcval runtime tests cannot be executed because `libsot.so` / `sc`/`scaddins`
266  are not built (GCC 15.2 vs AOO 510 ABI mismatch, no older GCC, no sudo).
267- `SG.DATEDIF-010-md` expected value corrected to `26` (borrow from start
268  month January: `10 + 31 - 15`) and DATEDIF regression coverage expanded in
269  `test/odf-validate/calcval-cases14.tsv`.
270
271The H.3.1 notes above describe the code as of 62e7f86e31. Of those, the
272end-of-month substitution, the reverse-interval policy, the case-sensitive
273format, the "md" borrow from the start month and the `26` expectation for
274`SG.DATEDIF-010-md` no longer apply.
275
2762026-11-18: as implemented
277--------------------------
278
279`ScInterpreter::ScDateDif()` in `main/sc/source/core/tool/interpr2.cxx`.
280xls: BIFF 351 is read and written (`xlformula.cxx`, no import-only flag).
281xlsx: read through the `DATEDIF` row of `oox/source/xls/formulabase.cxx`.
282
283- Arguments popped as Format, EndDate, StartDate; both dates floored with
284  `::rtl::math::approxFloor()`; an error in an argument is passed on.
285- StartDate > EndDate: Err:502. Invalid `Date`: Err:502. Unknown format:
286  Err:502. The format is lowered with `ToLowerAscii()` first.
287- Complete months: `(y2-y1)*12 + (m2-m1)`, minus 1 when the end day is before
288  the start day.
289- "d": day difference. "m": complete months. "y": complete months / 12.
290  "ym": complete months % 12.
291- "md": `d2 - d1`; when negative, add the length of the month before the end
292  month. This is Excel's rule and can be negative: Jan 31 to Mar 1, 2024 is -1
293  (Excel 16 gives -1 too).
294- "yd": the end date is moved into the start year, or into the next year when
295  its month/day comes before the start's; Feb 29 moved into a common year
296  becomes Mar 1. Result: that date minus StartDate. This is Excel's rule:
297  May 3, 1995 to Apr 28, 2009 is 361 (Excel 361).
298
299Checked against Excel 16 on 630 random and hand-picked cases: one difference,
300"yd" from Feb 29, 2020 to Mar 1, 2023 (AOO 1, Excel 0). Tests: the `DATEDIF.*`
301rows at the end of `test/odf-validate/calcval-cases14.tsv`, all matching on
302the Mac and Linux builds.
303