{"record":{"id":"f4ab7386bb11e0ca","repo":"iOfficeAI/OfficeCLI","slug":"cannot-store-datetext-as-date-excel-does-not","errorCode":null,"errorMessage":"Cannot store '{dateText}' as date; Excel does not support dates before 1900-01-01 (serial epoch is 1899-12-30). Use type=string to keep the literal text.","messagePattern":"Cannot store '(.+?)' as date; Excel does not support dates before 1900-01-01 \\(serial epoch is 1899-12-30\\)\\. Use type=string to keep the literal text\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs","lineNumber":742,"sourceCode":"                            $\"Cannot store '{cell.CellValue?.Text}' as number; value must be a finite numeric literal. \" +\n                            \"Use type=string to keep the literal text.\");\n                }\n                // CONSISTENCY(cell-type-parity): mirror Set's value auto-detect\n                // path (ExcelHandler.Set.cs lines 1025-1033) — parse the cell\n                // value as an ISO date and write it back as an OADate double so\n                // Excel renders it as a real date instead of a literal string.\n                if (cellType.Equals(\"date\", StringComparison.OrdinalIgnoreCase))\n                {\n                    var dateText = cell.CellValue?.Text?.Trim();\n                    // R13-2: accept ISO date-with-time (T separator) as well.\n                    if (!string.IsNullOrEmpty(dateText)\n                        && TryParseIsoDateFlexible(dateText, out var dt))\n                    {\n                        // Mirrors Set's pre-1900 guard: Excel's serial epoch is\n                        // 1899-12-30; earlier dates round-trip as the epoch and\n                        // mislead the user. Reject instead of silently clamping.\n                        if (dt < new System.DateTime(1900, 1, 1))\n                            throw new ArgumentException(\n                                $\"Cannot store '{dateText}' as date; Excel does not support dates before 1900-01-01 \" +\n                                $\"(serial epoch is 1899-12-30). Use type=string to keep the literal text.\");\n                        cell.CellValue = new CellValue(\n                            ExcelDataFormatter.ToExcelSerial(dt, IsWorkbookDate1904()).ToString(System.Globalization.CultureInfo.InvariantCulture));\n                    }\n                    else if (!string.IsNullOrEmpty(dateText))\n                    {\n                        // BUG-FIX(B10): if user said type=date but the value isn't\n                        // parseable, refuse to leave a date-shaped string in a\n                        // numeric-styled cell — that produces invalid OOXML.\n                        throw new ArgumentException(\n                            $\"Cannot store '{dateText}' as date; value must be ISO 8601 (yyyy-MM-dd) \" +\n                            $\"and represent a real calendar day. Use type=string to keep the literal text.\");\n                    }\n                    // Apply a default date number format unless the caller\n                    // already supplied one — matches Set's type=date guard.\n                    if (!properties.ContainsKey(\"numberformat\")\n                        && !properties.ContainsKey(\"numfmt\")","sourceCodeStart":724,"sourceCodeEnd":760,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs#L724-L760","documentation":"Excel's serial-date epoch is 1899-12-30, and dates earlier than 1900-01-01 are not representable — they round-trip as the epoch and silently mislead the user. When type=date is supplied and the parsed value is before 1900-01-01, this guard rejects it instead of silently clamping, mirroring Set's pre-1900 guard.","triggerScenarios":"Add(\"/Sheet1/A1\",\"cell\",pos,{[\"type\"]=\"date\",[\"value\"]=\"1899-12-31\"}); value=\"1850-01-01\"; value=\"1066-10-14\"; any ISO date that TryParseIsoDateFlexible parses to a DateTime < 1900-01-01.","commonSituations":"Historical data (birth dates before 1900, genealogical records, archival dates); importing a dataset with mixed-era dates; a default/placeholder sentinel date like 0001-01-01.","solutions":["Store pre-1900 dates as text with type=string, since Excel cannot represent them as serial dates.","Filter or transform pre-1900 values before the call (e.g. clamp to a sentinel string like 'pre-1900').","Validate the year >= 1900 when type=date is intended."],"exampleFix":"// before\nhandler.Add(\"/Sheet1/A1\", \"cell\", null, new() { [\"type\"] = \"date\", [\"value\"] = \"1850-01-01\" });\n// after\nhandler.Add(\"/Sheet1/A1\", \"cell\", null, new() { [\"type\"] = \"string\", [\"value\"] = \"1850-01-01\" });","handlingStrategy":"validation","validationCode":"if (props.GetValueOrDefault(\"type\")?.Equals(\"date\", StringComparison.OrdinalIgnoreCase) == true)\n{\n    if (DateTime.TryParse(props.GetValueOrDefault(\"value\"), out var dt) && dt < new DateTime(1900,1,1))\n        throw new ArgumentException(\"date is before Excel's 1900-01-01 epoch\");\n}","typeGuard":"static bool IsExcelRepresentableDate(string? s) =>\n    DateTime.TryParse(s, out var dt) && dt >= new DateTime(1900, 1, 1);","tryCatchPattern":"try { h.Add(parentPath, \"cell\", pos, props); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"before 1900-01-01\"))\n{ /* store as type=string instead */ }","preventionTips":["Filter pre-1900 dates out of date-typed columns.","Store historical/genealogical dates as text (type=string).","Validate year >= 1900 before setting type=date."],"tags":["excel","xlsx","cell","date","epoch","validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}