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