Date Validation

[alfligno]

Does date validation works? I read on the API that there is a TYPE_DATE but don't know how to use it, but I already tried

$validation=$sheet->getCell('A3')->getDataValidation();
$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE );
$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP );
$validation->setAllowBlank(true);
$validation->setOperator('isValidDate');
$validation->setErrorTitle('Oops!');
$validation->setError('Invalid date.');

But it seems like its not working, it does not open the datepicker

How do I setup the validation for date?

Does date validation works?

[alfligno]

Tested this Article but still not working, I added setAllowBlank and setting the setFormatCode as FORMAT_DATE_DMYSLASH on the cell but still won't work T_T

[franfoukal]

Same error here. Has anyone solved this? There is no info about how to use this validation type.

[alfligno]

I guess the date validation is working, but the MS or Google always turns it to other format or worst, it removes the format, its no hope for me, I also read their documentation and hoping to find some answers but no luck to me, In my experience for Google Excel, the date validation will work if it chooses the right date validation but formatting and validation will not work on this package as it will be converted on the other way, other validation works

[alfligno]

Also I don't think this package is still active 👍

[incentfit]

I believe the issue has to do with the operator. There doesn't seem to be any support for an "Is Valid Date" operator to match what Excel has when you try to set date validation on a cell manually.

[oleibman]

I don't think Excel allows "Is Valid Date". It does, however, allow, for example, "Is a date >= value", which might be good enough for your purposes.

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $validation=$sheet->getCell('A3')->getDataValidation();
        $validation->setType(DataValidation::TYPE_DATE);
        $validation->setErrorStyle(DataValidation::STYLE_STOP);
        $validation->setAllowBlank(true);
        $validation->setOperator(DataValidation::OPERATOR_GREATERTHANOREQUAL);
        $validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::formattedPHPtoExcel(1908, 6, 15));
        $validation->setErrorTitle('Oops!');
        $validation->setError('Invalid date.');
        $validation->setShowErrorMessage(true);

My example uses 1908-06-15 as the lower bound, and will work the same whether your Calendar starts in 1900 or 1904. Note that there isn't really a Date type in Excel, so any reasonable number can be used as the lower bound (and can be entered into the cell as an integer/float rather than in the expected date format). So I could simply use 3089 as formula1 (on a 1900-based spreadsheet), and in Excel you can likewise enter any value >= 3089 as an acceptable value to the cell rather than entering it as a date. If you enter the value as an integer/float, Excel will automatically format it as a date; in PhpSpreadsheet, you will need to explicitly set a date format for the cell.

[oleibman]

Closing. No update in 4 months, no reason to think suggested solution isn't adequate.