Repository navigation
Calc: text-to-number and text-to-date coercion always uses en-US separators; a German workbook gets #VALUE! or silently 1000× values #889
Description
Summary. Excel coerces text operands with the regional settings (de-DE: , decimal, . thousands, dd.mm.yyyy). GridCraft always parses en-US. Meter-reading sheets in my corpus store readings as text ("107,689", "0,001" factors, "1.330,00" amounts, "31.01.2021" headers): =HKVs[@[Abl. alt]]*HKVs[@[Faktor alt]] gives 107689 instead of 107.689 (silently, no error), =+B11/C11 with B11 = "1.330,00" gives #VALUE! instead of 150.57, =YEAR(Tabelle1[[#Headers],[31.01.2021]]) gives #VALUE! instead of 2021 (2 813 cells in one file). There is no region setting yet (#361; #367 asks for German function names), so this is the calculation side of that gap.
Steps to reproduce. Fixture gc_coerce_de.xlsx (download) (5 KB): A1:A4 text cells 107,689, 1.330,00, 0,001, 1,234.5; B1 =A1*1, B2 =A2/2, B3 =A3*1000, B4 =A4*1, C1 =VALUE(A1), C2 =VALUE(A2), C3 =A1+0, C4 =SUM(A1:A3), C5 =--A2.
gridcraft-cli cat gc_coerce_de.xlsx --range A1:C5 --csv
Expected (Excel 365 de-DE, measured via COM). B1 107.689 · B2 665 · B3 1 · B4 #VALUE! · C1 107.689 · C2 1330 · C3 107.689 · C4 0 · C5 1330. (An en-US Excel gives B1 107689, B2 #VALUE!, B3 1000, B4 1234.5.)
Actual (0.5.0 and 0.6.1). B1 107689 · B2 #VALUE! · B3 1000 · B4 1234.5 · C1 107689 · C2 #VALUE! · C3 107689 · C4 0 · C5 #VALUE! — en-US everywhere, regardless of the OS locale.
Analysis. Suggest one regional-settings source (decimal/thousands/list separator, short date order; default from the OS locale, overridable in Options — #361) used by implicit coercion, VALUE, DATEVALUE, the NUMBERVALUE defaults, TEXT, number entry and CSV import. The silent 1000× case is the dangerous one ("wrong numbers are worse than missing features", docs/gaps.md §6).
Environment. GridCraft 0.6.1 (gridcraft-0.6.1-windows-x64-portable.zip, gridcraft-cli 0.6.1) — identical result on 0.5.0, where the corpus run was made; Windows 11 25H2 (build 26200); Excel 365 (16.0, de-DE) via COM. Source reference: main @ 45221f5 (13 Oct).
Tested and written by Claude (Anthropic) on behalf of Udo Funk, who reviews every report before it is posted. Fixtures: https://github.com/KNUFFIXX/artcraft-fixtures (gridcraft/, MIT OR Apache-2.0; every file is synthetic, built by the generator in the same folder, no real data). Found in a run over 663 real-world workbooks, see the report issue.
Activity
sommestad commentedon Oct 15, 2026
Swedish (sv-SE) measurements for the same gap, since the fix will need more than one region's separators.
Setup. Excel for Mac 16.114 with macOS region Sweden (decimal
,, thousands separator a space, short dateyyyy-mm-dd), read back through AppleScript;gridcraft-cli 0.7.0(macOS universal) on the same file. Synthetic fixture built with openpyxl (no Excel-made file): column A holds text cells (@format), B and C formulas on them.107,689=A1*1=VALUE(A1)1 330,00(space)=A2/2=VALUE(A2)=--A20,001=A3*10001,234.5=A4*11 330,00(no-break space U+00A0)=A5/22026-10-15=A6+1=YEAR(A6)15/10/2026=A7+112,5%=A8*2=VALUE(A8)1.5=A9*11.330,00(de-DE spelling)=A10/2=SUM(A1:A3)So in a Swedish workbook the same formulas give either a silent 1000× value (
107,689→ 107689,0,001→ 1) or#VALUE!where Excel returns a number, and the en-US spellings1,234.5and1.5become numbers where Excel sv-SE says#VÄRDEFEL!. ISO dates are the one case that already agrees, because the sv-SE short date isyyyy-mm-dd.Two details worth keeping in mind for the fix:
1 330,00→ 1330) but a no-break space is not, at least in this Excel for Mac build. Excel itself inserts U+00A0 when it formats numbers, so a text cell copied from a formatted cell can still fail on Excel's side; GridCraft only needs to match the regular-space case.12,5%coerces to 0,125, so the percent suffix goes through the same region-aware path.The fixture is ten rows and can be rebuilt from the table (text in A1:A10 with the
@number format, the formulas in B and C); happy to attach the .xlsx if a maintainer wants it in a fixtures repo. The entry/display side of the same region (;arguments, decimal commas, Swedish function names) is going into a table on top of #443, see #430.KNUFFIXX commentedon Oct 15, 2026
One more place where a German workbook is evaluated with en-US rules, measured on 0.7.0:
TEXTformat codes. A German Excel keeps the format string ofTEXTas the user typed it, with German date letters (Tday,Jyear;Mmonth as in English) and German separators, and evaluates it that way; GridCraft reads the same string with the English codes. A date formatted with"TT.MM.JJJJ"comes back asTT.10.JJJJinstead of16.10.2023. In the corpus behind #891 (400 workbooks) this is rarer than the coercion case above (2 workbooks, 150TEXTcalls with German codes, shared formulas counted once), but it changes visible text without an error.Fixture
gc_text_codes_de.xlsx(KNUFFIXX/artcraft-fixtures,gridcraft/; standard library only; column B holds the values Excel cached): sheetCodes, A1 = 16 Oct 2023 (a Monday), A2 = 1234.5;B1
=TEXT(A1,"TT.MM.JJJJ")· B2=TEXT(A1,"JJJJ-MM-TT")· B3=TEXT(A1,"TTTT")· B4=TEXT(A1,"MMMM JJJJ")· B5=TEXT(A2,"#.##0,00")· B6=TEXT(A2,"0,0")· B7=TEXT(A1,"yyyy-mm-dd").16.10.2023TT.10.JJJJ2023-10-16JJJJ-10-TTMontagTTTTOktober 2023October JJJJ1.234,501234.50,001234,51,235yyyy-00-dd2023-10-16gridcraft-cli check gc_text_codes_de.xlsx --jsonlists 7 mismatches. B7 shows that the same German Excel does not read English codes either, so which reading is right depends on the region of whoever evaluates the file; a regional-settings source as suggested above (withTEXTamong its users) would cover both.Environment: GridCraft 0.7.0 windows-x64 portable, Windows 11 25H2 (build 26200), Excel for Microsoft 365 16.0.20601 de-DE via COM.
Tested and written by Claude (Anthropic). Posted for Udo Funk (@KNUFFIXX), emotional support human, who reads every report after it goes out.