SPB Git

Repository Public

COMPATIBILITY.md 6.9 KB

# FunctionsText — compatibility notes

Text and date/time worksheet functions of ORIX Sheet, registered with TextFunctions.register(into:locale:). 64 functions, each with signature, summary and category.

# Functions

Text (41) — TEXT, VALUE, NUMBERVALUE, FIXED, DOLLAR, T, CONCAT, CONCATENATE, TEXTJOIN, LEFT, RIGHT, MID, LEN, LOWER, UPPER, PROPER, TRIM, CLEAN, REPT, EXACT, FIND, SEARCH, SUBSTITUTE, REPLACE, CHAR, CODE, UNICHAR, UNICODE, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, VALUETOTEXT, ARRAYTOTEXT, LEFTB, RIGHTB, MIDB, LENB, REGEXTEST, REGEXEXTRACT, REGEXREPLACE, ENCODEURL.

Date & time (23) — DATE, TIME, DATEVALUE, TIMEVALUE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY (types 1–3, 11–17), WEEKNUM (1, 2, 11–17, 21), ISOWEEKNUM, EDATE, EOMONTH, DAYS, DAYS360 (US and European), DATEDIF (Y, M, D, MD, YM, YD), YEARFRAC (bases 0–4), NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL (weekend numbers 1–7 and 11–17, 7-character strings, holidays as ranges, arrays or date text).

# Semantics

  • Character positions count UTF-16 code units, like Excel: every BMP character is 1, an emoji is 2 (LEN("😀") = 2). Cutting through a surrogate pair yields U+FFFD instead of a lone surrogate. The B variants behave like their non-B counterparts (single-byte-language behaviour).
  • Coercion: numbers become text with General formatting (15 digits), booleans become TRUE/FALSE, blanks become empty text or 0. Date arguments accept serial numbers and date/time text.
  • Lifting: scalar arguments are lifted element-wise over arrays and ranges by the engine; CONCAT, TEXTJOIN, ARRAYTOTEXT, the delimiter arguments of TEXTBEFORE/TEXTAFTER/TEXTSPLIT and the holiday arguments take whole ranges and arrays.
  • CHAR / CODE use Windows-1252 (CHAR(128) = €), the encoding of Excel for Windows, so files behave the same everywhere; CODE of a character outside the code page is 63 (?).
  • SEARCH supports ?, * and ~ escapes, case-insensitively. REGEX* functions use ICU regular expressions (NSRegularExpression); replacement templates use $1.
  • TEXT renders through SheetFormat; General is limited to 11 characters like Excel; a negative date is #VALUE!.
  • Dates respect ctx.date1904 and the 1900 leap-year bug: DATE(1900,2,29) = 60, DAY(60) = 29, WEEKDAY(1) = 1 (Excel's fictitious Sunday), EOMONTH(32,0) = 60. Valid serials are 0 … 2 958 465 (#NUM! outside).
  • Date text (DATEVALUE, TIMEVALUE, VALUE, date arguments): m/d/yyyy, m-d-yy, ISO yyyy-mm-dd, yyyy/mm/dd, d-mmm-yy, d mmmm yyyy, mmmm d, yyyy, mmm yyyy, m/d (current year), with an optional time (h:mm, h:mm:ss, AM/PM). Month names are English plus those of the format locale. Two-digit years: 00–29 → 2000s, 30–99 → 1900s.
  • HOUR / MINUTE / SECOND round the time to the nearest second first, like Excel.
  • YEARFRAC implements Excel's actual algorithm (US 30/360 with the end-of-February rules, and the actual/actual rule: one-year spans use 365 or 366, longer spans the average year length).
  • DATEDIF reproduces the documented quirks: "MD" places the start day in the month before the end date and lets it overflow (31 Jan → 1 Mar gives −2); "YD" moves the start date into the end year.

# Validation

Tests/FunctionsTextTests/Fixtures/function_references.json holds 7 951 reference cases generated by Tests/FunctionsTextTests/Generators/generate_function_fixtures.py:

Source Cases Meaning
lo 3 565 formula recalculated by headless LibreOffice 26.8 (en-US profile)
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
py 2 362 Python reference (re, str) for the functions LibreOffice does not implement: VALUETOTEXT, ARRAYTOTEXT, REGEXTEST, REGEXEXTRACT, REGEXREPLACE
excel 234 LibreOffice deviates from Excel's documented behaviour; the Excel value is stored with a note, the LibreOffice value is kept in lo

Every function has reference cases and all of them pass (the test also fails when a registered function has no reference). About 310 hand-written expectations add Microsoft's documented examples, array lifting, ranges, blanks, errors, UTF-16 positions, the 1900 leap bug, the 1904 system and the fr-CA locale.

# LibreOffice results replaced by Excel's (source excel)

  • WEEKNUM (types other than 21) on the last days of December: Excel returns 53/54, LibreOffice wraps to 1 (149 cases, Python reference).
  • YEARFRAC basis 0 with a start date on the last day of February (Python reference of Excel's algorithm).
  • Booleans as text: Excel TRUE/FALSE, LibreOffice 1/0 (LEFT, LEN, UPPER, CONCAT, TEXTJOIN…).
  • Invalid arguments: Excel #NUM!, LibreOffice #VALUE! (WEEKDAY/WEEKNUM types, DATEDIF units, YEARFRAC basis, weekend numbers…).
  • Negative serials and dates past year 9999: #NUM! in Excel, computed by LibreOffice.
  • TIME truncates its arguments and limits them to 32767; DATE adds 1900 to years below 1900.
  • FIXED rounds the decimal value (0.285 → 0.29).
  • FIND/SEARCH with empty find_text return start_num; CHAR(0), UNICHAR(0), CODE("") are #VALUE!.
  • LEN(UNICHAR(128512)) = 2 (UTF-16); CLEAN keeps character 127; ENCODEURL keeps . (Microsoft's documented example; ~ is kept too, by RFC 3986, unverified).
  • TEXT(TRUE,"0") = "TRUE"; TEXT(x,"General") is limited to 11 characters.
  • DATEVALUE accepts yyyy/mm/dd, d mmmm yyyy, m-d-yyyy; two-digit year 30 is 1930.
  • TEXTBEFORE/TEXTAFTER with instance 0 and TEXTSPLIT with an empty delimiter are #VALUE!; NUMBERVALUE("") = 0.

# Known deviations from Excel

  • DOLLAR returns -$1,234.57 for negative numbers (current Excel and LibreOffice); Excel's documentation still describes ($1,234.57).
  • Date text is parsed in month/day/year order regardless of the locale (ISO dates are always accepted); day-first locales are not supported yet. Dots are not date separators.
  • CHAR/CODE follow Windows-1252, not Mac OS Roman as Excel for Mac does.
  • UPPER/LOWER/PROPER use Unicode case mapping (UPPER("straße") = STRASSE).
  • The REGEX functions use ICU syntax; Excel uses PCRE2. Common syntax is identical; PCRE-only constructs differ.
  • TEXTBEFORE/TEXTAFTER count non-overlapping delimiter occurrences; overlapping delimiters may differ from Excel.
  • TEXTSPLIT uses the top-left value of an array text argument.
  • DATEDIF "YD" follows the algorithm LibreOffice shares with Excel's documentation; Excel has reported discrepancies for some leap-year spans that are not reproduced.
  • HOUR/MINUTE/SECOND rounding at half-second boundaries and DAYS truncating its arguments follow Excel's observed behaviour; LibreOffice differs, so these cases are covered by hand-written tests only.
  • No result is validated against a live Excel: references are LibreOffice, Python and Microsoft's documentation.
Files main
Menu

Theme