PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

#NUM!

#NUM!

Error message

#NUM!

What it means

PhpSpreadsheet throws this when the second (style / return-type) argument of the Excel WEEKDAY function is numeric but outside the allowed 1-3 range. validateStyle() in DateTimeExcel\Week casts the value to int and any result below 1 or above 3 throws Calculation\Exception('#NUM!'); the public Week::day() wrapper catches it and returns the string '#NUM!' as the cell/result value. Excel itself only supports styles 1 (Sunday=1..7), 2 (Monday=1..7) and 3 (Monday=0..6).

Source

Thrown at src/PhpSpreadsheet/Calculation/DateTimeExcel/Week.php:199

                $DoW = self::dow0Becomes7($DoW) - 1;

                break;
        }

        return $DoW;
    }

    /**
     * @param mixed $style expect int
     */
    private static function validateStyle(mixed $style): int
    {
        if (!is_numeric($style)) {
            throw new Exception(ExcelError::VALUE());
        }
        $style = (int) $style;
        if (($style < 1) || ($style > 3)) {
            throw new Exception(ExcelError::NAN());
        }

        return $style;
    }

    private static function dow0Becomes7(int $DoW): int
    {
        return ($DoW === 0) ? 7 : $DoW;
    }

    /**
     * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer),
     *                                    PHP DateTime object, or a standard date string
     */
    private static function apparentBug(mixed $dateValue): bool
    {
        if (SharedDateHelper::getExcelCalendar() !== SharedDateHelper::CALENDAR_MAC_1904) {
            if (is_bool($dateValue)) {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Set the WEEKDAY style to 1, 2 or 3 (e.g. =WEEKDAY(A1, 2)); no other value is supported.
2. If you wanted a week-start method like 11-17 or 21, that belongs to WEEKNUM: use =WEEKNUM(date, method) / Week::number($date, 21), or ISOWEEKNUM for ISO weeks.
3. Sanitize user-supplied style values before building the formula (cast to int and clamp/reject outside 1-3).
4. When evaluating untrusted formulas, detect the failure by checking the calculated result for the literal string '#NUM!'.

Example fix

// before - returns '#NUM!'
$value = \PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel\Week::day('2026-08-16', 11);

// after - style 2: Monday=1 .. Sunday=7
$value = \PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel\Week::day('2026-08-16', 2);

$style = (int) $style;
if ($style < 1 || $style > 3) {
    throw new \InvalidArgumentException('WEEKDAY style must be 1, 2 or 3, got ' . $style);
}
$value = \PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel\Week::day($dateValue, $style);

/** WEEKDAY only accepts styles 1, 2 or 3. */
function isValidWeekdayStyle(mixed $style): bool
{
    return is_numeric($style) && (int) $style >= 1 && (int) $style <= 3;
}

When it happens

Trigger: Calling =WEEKDAY(A1, 4), =WEEKDAY(A1, 0) or =WEEKDAY(A1, 11) in a worksheet; calling Week::day($dateValue, 11) or passing an array of styles containing an out-of-range entry. Numeric strings like '5' are cast to int and still fail the 1-3 check; only non-numeric styles give #VALUE! instead.

Common situations: Confusing WEEKDAY's style with WEEKNUM's method codes (which do accept 11-17 and 21) and passing 11-17 to WEEKDAY; expecting 0 to be a valid zero-based style; formulas imported from other spreadsheet tools or assembled by string concatenation with a wrong constant.

Related errors

• #VALUE!
• #VALUE!
• #NUM!
• #VALUE!
• #VALUE!

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