Repository Public
1# FunctionsText — compatibility notes23Text and date/time worksheet functions of ORIX Sheet, registered with4`TextFunctions.register(into:locale:)`. 64 functions, each with signature, summary and category.56## Functions78**Text (41)** — TEXT, VALUE, NUMBERVALUE, FIXED, DOLLAR, T, CONCAT, CONCATENATE, TEXTJOIN, LEFT, RIGHT, MID,9LEN, LOWER, UPPER, PROPER, TRIM, CLEAN, REPT, EXACT, FIND, SEARCH, SUBSTITUTE, REPLACE, CHAR, CODE, UNICHAR,10UNICODE, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, VALUETOTEXT, ARRAYTOTEXT, LEFTB, RIGHTB, MIDB, LENB, REGEXTEST,11REGEXEXTRACT, REGEXREPLACE, ENCODEURL.1213**Date & time (23)** — DATE, TIME, DATEVALUE, TIMEVALUE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY14(types 1–3, 11–17), WEEKNUM (1, 2, 11–17, 21), ISOWEEKNUM, EDATE, EOMONTH, DAYS, DAYS360 (US and European),15DATEDIF (Y, M, D, MD, YM, YD), YEARFRAC (bases 0–4), NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL16(weekend numbers 1–7 and 11–17, 7-character strings, holidays as ranges, arrays or date text).1718## Semantics1920- **Character positions** count UTF-16 code units, like Excel: every BMP character is 1, an emoji is 221 (`LEN("😀")` = 2). Cutting through a surrogate pair yields U+FFFD instead of a lone surrogate.22 The `B` variants behave like their non-`B` counterparts (single-byte-language behaviour).23- **Coercion**: numbers become text with General formatting (15 digits), booleans become `TRUE`/`FALSE`,24 blanks become empty text or 0. Date arguments accept serial numbers and date/time text.25- **Lifting**: scalar arguments are lifted element-wise over arrays and ranges by the engine; CONCAT, TEXTJOIN,26 ARRAYTOTEXT, the delimiter arguments of TEXTBEFORE/TEXTAFTER/TEXTSPLIT and the holiday arguments take27 whole ranges and arrays.28- **CHAR / CODE** use Windows-1252 (CHAR(128) = €), the encoding of Excel for Windows, so files behave the29 same everywhere; CODE of a character outside the code page is 63 (`?`).30- **SEARCH** supports `?`, `*` and `~` escapes, case-insensitively. **REGEX\*** functions use ICU regular31 expressions (NSRegularExpression); replacement templates use `$1`.32- **TEXT** renders through SheetFormat; `General` is limited to 11 characters like Excel; a negative date is #VALUE!.33- **Dates** respect `ctx.date1904` and the 1900 leap-year bug: DATE(1900,2,29) = 60, DAY(60) = 29,34 WEEKDAY(1) = 1 (Excel's fictitious Sunday), EOMONTH(32,0) = 60. Valid serials are 0 … 2 958 465 (#NUM! outside).35- **Date text** (DATEVALUE, TIMEVALUE, VALUE, date arguments): `m/d/yyyy`, `m-d-yy`, ISO `yyyy-mm-dd`,36 `yyyy/mm/dd`, `d-mmm-yy`, `d mmmm yyyy`, `mmmm d, yyyy`, `mmm yyyy`, `m/d` (current year), with an optional37 time (`h:mm`, `h:mm:ss`, AM/PM). Month names are English plus those of the format locale. Two-digit years:38 00–29 → 2000s, 30–99 → 1900s.39- **HOUR / MINUTE / SECOND** round the time to the nearest second first, like Excel.40- **YEARFRAC** implements Excel's actual algorithm (US 30/360 with the end-of-February rules, and the41 actual/actual rule: one-year spans use 365 or 366, longer spans the average year length).42- **DATEDIF** reproduces the documented quirks: "MD" places the start day in the month before the end date and43 lets it overflow (31 Jan → 1 Mar gives −2); "YD" moves the start date into the end year.4445## Validation4647`Tests/FunctionsTextTests/Fixtures/function_references.json` holds 7 951 reference cases generated by48`Tests/FunctionsTextTests/Generators/generate_function_fixtures.py`:4950| Source | Cases | Meaning |51|---|---|---|52| `lo` | 3 565 | formula recalculated by headless LibreOffice 26.8 (en-US profile) |53| `lo+py` | 1 790 | LibreOffice result that also equals an independent Python reference (`datetime`, `dateutil`; the generator aborts on any disagreement): YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM, EDATE, EOMONTH, DAYS, NETWORKDAYS, WORKDAY |54| `py` | 2 362 | Python reference (`re`, `str`) for the functions LibreOffice does not implement: VALUETOTEXT, ARRAYTOTEXT, REGEXTEST, REGEXEXTRACT, REGEXREPLACE |55| `excel` | 234 | LibreOffice deviates from Excel's documented behaviour; the Excel value is stored with a note, the LibreOffice value is kept in `lo` |5657Every function has reference cases and all of them pass (the test also fails when a registered function has58no reference). About 310 hand-written expectations add Microsoft's documented examples, array lifting,59ranges, blanks, errors, UTF-16 positions, the 1900 leap bug, the 1904 system and the fr-CA locale.6061### LibreOffice results replaced by Excel's (source `excel`)6263- WEEKNUM (types other than 21) on the last days of December: Excel returns 53/54, LibreOffice wraps to 1 (149 cases, Python reference).64- YEARFRAC basis 0 with a start date on the last day of February (Python reference of Excel's algorithm).65- Booleans as text: Excel `TRUE`/`FALSE`, LibreOffice `1`/`0` (LEFT, LEN, UPPER, CONCAT, TEXTJOIN…).66- Invalid arguments: Excel #NUM!, LibreOffice #VALUE! (WEEKDAY/WEEKNUM types, DATEDIF units, YEARFRAC basis, weekend numbers…).67- Negative serials and dates past year 9999: #NUM! in Excel, computed by LibreOffice.68- TIME truncates its arguments and limits them to 32767; DATE adds 1900 to years below 1900.69- FIXED rounds the decimal value (0.285 → 0.29).70- FIND/SEARCH with empty `find_text` return `start_num`; CHAR(0), UNICHAR(0), CODE("") are #VALUE!.71- LEN(UNICHAR(128512)) = 2 (UTF-16); CLEAN keeps character 127; ENCODEURL keeps `.` (Microsoft's documented example; `~` is kept too, by RFC 3986, unverified).72- TEXT(TRUE,"0") = "TRUE"; TEXT(x,"General") is limited to 11 characters.73- DATEVALUE accepts `yyyy/mm/dd`, `d mmmm yyyy`, `m-d-yyyy`; two-digit year 30 is 1930.74- TEXTBEFORE/TEXTAFTER with instance 0 and TEXTSPLIT with an empty delimiter are #VALUE!; NUMBERVALUE("") = 0.7576## Known deviations from Excel7778- DOLLAR returns `-$1,234.57` for negative numbers (current Excel and LibreOffice); Excel's documentation79 still describes `($1,234.57)`.80- Date text is parsed in month/day/year order regardless of the locale (ISO dates are always accepted);81 day-first locales are not supported yet. Dots are not date separators.82- CHAR/CODE follow Windows-1252, not Mac OS Roman as Excel for Mac does.83- UPPER/LOWER/PROPER use Unicode case mapping (`UPPER("straße")` = `STRASSE`).84- The REGEX functions use ICU syntax; Excel uses PCRE2. Common syntax is identical; PCRE-only constructs differ.85- TEXTBEFORE/TEXTAFTER count non-overlapping delimiter occurrences; overlapping delimiters may differ from Excel.86- TEXTSPLIT uses the top-left value of an array `text` argument.87- DATEDIF "YD" follows the algorithm LibreOffice shares with Excel's documentation; Excel has reported88 discrepancies for some leap-year spans that are not reproduced.89- HOUR/MINUTE/SECOND rounding at half-second boundaries and DAYS truncating its arguments follow Excel's90 observed behaviour; LibreOffice differs, so these cases are covered by hand-written tests only.91- No result is validated against a live Excel: references are LibreOffice, Python and Microsoft's documentation.92