What causes "Invalid Operation for Date Value CF Rule Wizard" in PHPOffice/PhpSpreadsheet?

PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

Invalid Operation for Date Value CF Rule Wizard

Error message

Invalid Operation for Date Value CF Rule Wizard

What it means

The DateValue (dates occurring) wizard works through __call with a fixed MAGIC_OPERATIONS map: yesterday, today, tomorrow, lastSevenDays (alias last7Days), lastWeek, thisWeek, nextWeek, lastMonth, thisMonth, nextMonth. Excel's timePeriod rule types define no year or quarter periods, so names like thisYear(), lastYear() or thisQuarter() throw Invalid Operation.

Source

Thrown at src/PhpSpreadsheet/Style/ConditionalFormatting/Wizard/DateValue.php:102

        if ($conditional->getConditionType() !== Conditional::CONDITION_TIMEPERIOD) {
            throw new Exception('Conditional is not a Date Value CF Rule conditional');
        }

        $wizard = new self($cellRange);
        $wizard->style = $conditional->getStyle();
        $wizard->stopIfTrue = $conditional->getStopIfTrue();
        $wizard->operator = $conditional->getText();

        return $wizard;
    }

    /**
     * @param mixed[] $arguments
     */
    public function __call(string $methodName, array $arguments): self
    {
        if (!isset(self::MAGIC_OPERATIONS[$methodName])) {
            throw new Exception('Invalid Operation for Date Value CF Rule Wizard');
        }

        $this->operator(self::MAGIC_OPERATIONS[$methodName]);

        return $this;
    }
}

View on GitHub (pinned to 65b080eef4)

Solutions

Use only the supported period names: yesterday, today, tomorrow, lastSevenDays/last7Days, lastWeek, thisWeek, nextWeek, lastMonth, thisMonth, nextMonth
For year or quarter logic, build a Wizard\Expression rule with a formula such as YEAR(A1)=YEAR(TODAY())
Validate dynamic period names against the supported list
Map UI filter labels explicitly to supported methods instead of mechanical conversion

Example fix

// before
$wizard = (new Wizard('A1:E10'))->newRule(Wizard::DATES_OCCURRING);
$wizard->thisYear();   // throws: no year period exists

// after
$wizard->thisMonth();  // or yesterday/today/tomorrow/lastSevenDays/lastWeek/thisWeek/nextWeek/lastMonth/nextMonth
// for year logic use an expression rule instead:
// (new Wizard('A1:E10'))->newRule(Wizard::EXPRESSION)->formula('YEAR(A1)=YEAR(TODAY())');

Defensive patterns

Strategy: validation

Validate before calling

$allowed = ['yesterday', 'today', 'tomorrow', 'lastSevenDays', 'last7Days',
    'lastWeek', 'thisWeek', 'nextWeek', 'lastMonth', 'thisMonth', 'nextMonth'];
$period = $config['datePeriod'];
if (!in_array($period, $allowed, true)) {
    throw new InvalidArgumentException('Unsupported date period: ' . $period);
}
$wizard->$period();

Try / catch

use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;

try {
    $dateWizard->$period();
} catch (PhpSpreadsheetException $e) {
    // unsupported period such as thisYear/thisQuarter
    throw new InvalidArgumentException('Unsupported date period: ' . $period, 0, $e);
}

Prevention

Use only the eleven supported timePeriod method names
No year or quarter periods exist - build an Expression rule for those
Map UI date-filter labels to supported methods through an explicit whitelist
Method names are case-sensitive: last7Days works, last7days does not

When it happens

Trigger: ->thisYear() or ->lastYear() (no year period exists in the wizard); ->thisQuarter(); misspellings such as ->tommorow() or ->last7days() (case matters: the alias is last7Days).

Common situations: UIs offering date filters like 'this year' or 'this quarter' that do not map 1:1 to Excel timePeriod types; translating UI labels mechanically into method names.

Related errors

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