Create Xlsx Files With Past Date Data Validation

Ensure a user is entering a date in the past by coding a data validation in PhpSpreadsheet when creating an xlsx file. The current date, or any date in the future will be denied the entry, securing the cell will only contain values that are dates in the past.

Requirements:

Composer
PHP 7.2 or newer

Step 1.

Setup dependencies.

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

composer.json

Step 2.

Install phpspreadsheet.

$ composer install

command line

Step 3.

Create a new PHP file, and start coding.

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

/**
 * Set cell B3 with the "Enter a past date:"
 * string value, will serve as the 'Input Title'.
 */
$sheet->setCellValue('B3', 'Enter a past date:');

/**
 * Set the date yesterday as the default value for C3.
 *
 * The user will only be allowed to input date from
 * yesterday to any date in the past, and for any
 * invalid input, the date yesterday (default) will
 * be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date("m/d/Y", strtotime('yesterday')));

/**
 * Set the 'past date' validation on C3.
 */
$validation = $sheet->getCell('C3')->getDataValidation();

/**
 * Since the validation is for a 'past date',
 * set the validation type to 'Date'.
 */
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE);

/**
 * Set the operator to 'Less than' to
 * check for any past date value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_LESSTHAN);

/**
 * Set the current date as the 'Start date' for which
 * the 'past validation' will be based from.
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y')));

/**
 * Do not allow empty value.
 */
$validation->setAllowBlank(false);

/**
 * Display a cell 'note' about the
 * 'past date' validation.
 */
$validation->setShowInputMessage(true);

/**
 * Set the 'note' title.
 */
$validation->setPromptTitle('Note');

/**
 * Describe the note.
 */
$validation->setPrompt('Must be less than the date today.');

/**
 * Show error message if the data entered is invalid.
 */
$validation->setShowErrorMessage(true);

/**
 * Do not allow any other data to be entered
 * by setting the style to 'Stop'.
 */
$validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);

/**
 * Set descriptive error title.
 */
$validation->setErrorTitle('Invalid date.');

/**
 * Set the error message.
 */
$validation->setError('The given date is not less than the date today.');

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-past-date-data-validation.xlsx');

create-xlsx-files-with-past-date-data-validation.php

Test.

Run the following codes.

$ php create-xlsx-files-with-past-date-data-validation.php

command line

Result.

Open the generated file create-xlsx-files-with-past-date-data-validation.xlsx.

/**
 * Set cell B3 with the "Enter a future date:"
 * string value, will serve as the 'Input Title'.
 */
$sheet->setCellValue('B3', 'Enter a future date:');

/**
 * Set the date tomorrow as the default value for C3.
 *
 * The user will only be allowed to input date from
 * tomorrow to any date in the future, and for any
 * invalid input, the date tomorrow (default) will
 * be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date("m/d/Y", strtotime('tomorrow')));

/**
 * Set the 'future date' validation on C3.
 */
$validation = $sheet->getCell('C3')->getDataValidation();

Check the settings.

Click on Data.

Click on cell C3.

Click on ‘Data Validation’.

The ‘Data ValidationData Validation‘ window should show up.

Under the Settings Tab of the ‘Data Validation‘ window.

/**
 * Since the validation is for a 'past date',
 * set the validation type to 'Date'.
 */
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE);

/**
 * Set the operator to 'Less than' to
 * check for any past date value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_LESSTHAN);

Important:

The formula value must be set appropriately, otherwise the data validation will not work. And on this case, it must be converted to excel time format using the \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel method, this will ensure the data is properly formatted.

/**
 * Set the current date as the 'Start date' for which
 * the 'past validation' will be based from.
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y')));

/**
 * Do not allow empty value.
 */
$validation->setAllowBlank(false);

Under the Input Message Tab of the ‘Data Validation‘ window.

/**
 * Display a cell 'note' about the
 * 'past date' validation.
 */
$validation->setShowInputMessage(true);

/**
 * Set the 'note' title.
 */
$validation->setPromptTitle('Note');

/**
 * Describe the note.
 */
$validation->setPrompt('Must be less than the date today.');

Under the Error Alert Tab of the ‘Data Validation‘ window.

/**
 * Show error message if the data entered is invalid.
 */
$validation->setShowErrorMessage(true);

If the date entered is not a future data, the ‘Error Window‘ should appear.

/**
 * Do not allow any other data to be entered
 * by setting the style to 'Stop'.
 */
$validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);

/**
 * Set descriptive error title.
 */
$validation->setErrorTitle('Invalid date.');

/**
 * Set the error message.
 */
$validation->setError('The given date is not less than the date today.');