iOfficeAI/OfficeCLI · error · ArgumentException

Cannot store '{dateText}' as date; value must be ISO 8601 (y

Error message

Cannot store '{dateText}' as date; value must be ISO 8601 (yyyy-MM-dd) and represent a real calendar day. Use type=string to keep the literal text.

What it means

When type=date is supplied but the value is non-empty and cannot be parsed as an ISO 8601 date (yyyy-MM-dd, with optional T-separated time), this throws rather than leaving a date-shaped string in a numeric-styled cell — which would produce invalid OOXML. It mirrors Set's type=date guard so a non-parseable date value is rejected up front.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs:753

                    if (!string.IsNullOrEmpty(dateText)
                        && TryParseIsoDateFlexible(dateText, out var dt))
                    {
                        // Mirrors Set's pre-1900 guard: Excel's serial epoch is
                        // 1899-12-30; earlier dates round-trip as the epoch and
                        // mislead the user. Reject instead of silently clamping.
                        if (dt < new System.DateTime(1900, 1, 1))
                            throw new ArgumentException(
                                $"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.");
                        cell.CellValue = new CellValue(
                            ExcelDataFormatter.ToExcelSerial(dt, IsWorkbookDate1904()).ToString(System.Globalization.CultureInfo.InvariantCulture));
                    }
                    else if (!string.IsNullOrEmpty(dateText))
                    {
                        // BUG-FIX(B10): if user said type=date but the value isn't
                        // parseable, refuse to leave a date-shaped string in a
                        // numeric-styled cell — that produces invalid OOXML.
                        throw new ArgumentException(
                            $"Cannot store '{dateText}' as date; value must be ISO 8601 (yyyy-MM-dd) " +
                            $"and represent a real calendar day. Use type=string to keep the literal text.");
                    }
                    // Apply a default date number format unless the caller
                    // already supplied one — matches Set's type=date guard.
                    if (!properties.ContainsKey("numberformat")
                        && !properties.ContainsKey("numfmt")
                        && !properties.ContainsKey("format"))
                    {
                        properties["numberformat"] = "yyyy-mm-dd";
                    }
                }
            }
        }
        if (properties.TryGetValue("clear", out _))
        {
            cell.CellValue = null;
            cell.CellFormula = null;

View on GitHub (pinned to 1ced45e900)

Solutions

1. Pass the date in ISO 8601 form: yyyy-MM-dd (e.g. 2020-01-13) or yyyy-MM-ddTHH:mm:ss.
2. If the source date is in another format, parse and re-format to ISO before the call.
3. If the value is not a date, use type=string.

Example fix

// before
handler.Add("/Sheet1/A1", "cell", null, new() { ["type"] = "date", ["value"] = "13/01/2020" });
// after
var iso = DateTime.Parse("13/01/2020", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
handler.Add("/Sheet1/A1", "cell", null, new() { ["type"] = "date", ["value"] = iso });

if (props.GetValueOrDefault("type")?.Equals("date", StringComparison.OrdinalIgnoreCase) == true
    && props.TryGetValue("value", out var dv) && !string.IsNullOrEmpty(dv))
{
    if (!DateTime.TryParseExact(dv, new[]{"yyyy-MM-dd","yyyy-MM-ddTHH:mm:ss"}, CultureInfo.InvariantCulture, DateTimeStyles.None, out _))
        throw new ArgumentException("date value is not ISO 8601");
}

static bool IsIsoDate(string? s) =>
    !string.IsNullOrEmpty(s) && DateTime.TryParseExact(s, new[]{"yyyy-MM-dd","yyyy-MM-ddTHH:mm:ss"},
        CultureInfo.InvariantCulture, DateTimeStyles.None, out _);

try { h.Add(parentPath, "cell", pos, props); }
catch (ArgumentException ex) when (ex.Message.Contains("must be ISO 8601"))
{ /* re-format the source date to yyyy-MM-dd and retry, or use string */ }

When it happens

Trigger: Add("/Sheet1/A1","cell",pos,{["type"]="date",["value"]="13/01/2020"}); value="Jan 1 2020" (not ISO); value="2020/01/01" (slashes, not ISO dashes); value="2020-13-01" (invalid month).

Common situations: Locale-specific date formats (DD/MM/YYYY, month-name forms); values from a system that emits non-ISO dates; a timezone-offset suffix that TryParseIsoDateFlexible does not accept.

Related errors

• Cannot store '{dateText}' as date; Excel does not support da
• Unrecognized cell parent path segment '{cellSegments[1]}'. E
• Invalid cell reference: '{cellRef}'
• --prop shift={shiftVal} not valid for add cell. Use 'right'
• Cannot store '{properties.GetValueOrDefault("value") ?? prop

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/37266f1654b7db1f. Report an issue: GitHub.