PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

Invalid string $value supplied for datatype Date

Error message

Invalid string $value supplied for datatype Date

What it means

convertIsoDate() constructs a DateTime from the string, then rejects it if DateTime::getLastErrors() reports any warning or error (src/PhpSpreadsheet/Shared/Date.php:173). So the string parses syntactically but is semantically invalid — day out of range for the month, month 13, or other conditions PHP flags — and the same call paths as error 189 apply (setValueExplicit with TYPE_ISO_DATE, Ods reader).

Source

Thrown at src/PhpSpreadsheet/Shared/Date.php:173

        throw new PhpSpreadsheetException('Invalid timezone');
    }

    /**
     * @param mixed $value Converts a date/time in ISO-8601 standard format date string to an Excel
     *                         serialized timestamp.
     *                     See https://en.wikipedia.org/wiki/ISO_8601 for details of the ISO-8601 standard format.
     */
    public static function convertIsoDate(mixed $value, ?int $calendar = null): float|int
    {
        if (!is_string($value)) {
            throw new Exception('Non-string value supplied for Iso Date conversion');
        }

        $date = new DateTime($value);
        $dateErrors = DateTime::getLastErrors();

        if (is_array($dateErrors) && ($dateErrors['warning_count'] > 0 || $dateErrors['error_count'] > 0)) {
            throw new Exception("Invalid string $value supplied for datatype Date");
        }

        $newValue = self::dateTimeToExcel($date, $calendar);

        if (preg_match('/^\s*\d?\d:\d\d(:\d\d([.]\d+)?)?\s*(am|pm)?\s*$/i', $value) == 1) {
            $newValue = fmod($newValue, 1.0);
        }

        return $newValue;
    }

    /**
     * Convert a MS serialized datetime value from Excel to a PHP Date/Time object.
     *
     * @param float|int $excelTimestamp MS Excel serialized date/time value
     * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp,
     *                                           if you don't want to treat it as a UTC value
     *                                           Use the default (UTC) unless you absolutely need a conversion

View on GitHub (pinned to 65b080eef4)

Solutions

  1. Validate before binding: DateTime::createFromFormat('Y-m-d', $v) plus getLastErrors(), or checkdate($m, $d, $y)
  2. Normalize user input from its real source format (createFromFormat with the actual pattern) into Y-m-d
  3. In import pipelines, catch the exception, log the cell coordinate, and quarantine the row instead of aborting the batch

Example fix

// before
$cell->setValueExplicit('2023-02-30', DataType::TYPE_ISO_DATE); // throws

// after
$d = \DateTime::createFromFormat('Y-m-d', '2023-02-30');
$errs = \DateTime::getLastErrors();
if ($d === false || $errs['warning_count'] > 0 || $errs['error_count'] > 0) {
    throw new \InvalidArgumentException('Invalid date: 2023-02-30');
}
$cell->setValueExplicit('2023-02-30', DataType::TYPE_ISO_DATE);
Defensive patterns

Strategy: validation

Validate before calling

function toStrictIsoDate(string $value): ?string
{
    $d = \DateTime::createFromFormat('!Y-m-d', $value);
    $errs = \DateTime::getLastErrors();

    if ($d === false || $errs['warning_count'] > 0 || $errs['error_count'] > 0) {
        return null;
    }

    return $d->format('Y-m-d');
}

if (($iso = toStrictIsoDate($input)) === null) {
    return 'invalid date';
}
$cell->setValueExplicit($iso, DataType::TYPE_ISO_DATE);

Try / catch

try {
    $cell->setValueExplicit($input, DataType::TYPE_ISO_DATE);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    if (str_contains($e->getMessage(), 'datatype Date')) {
        rejectRow($rowNumber, $input);
    }
}

Prevention

When it happens

Trigger: $cell->setValueExplicit('2023-02-30', DataType::TYPE_ISO_DATE); '2024-13-01'; user-typed dates where a d/m/Y value was normalized as Y-m-d producing impossible month/day pairs.

Common situations: User-supplied form data bound as ISO dates; scraped or OCR'd data with invalid days; locale-mismatched parsing (m/d/Y vs d/m/Y) yielding invalid combinations; February 29 in non-leap years.

Related errors

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/74e545b3e14324cd. Report an issue: GitHub.