Create Xlsx Files With Date Not In Range Data Validation

Code the opposite logic of the “Date range data validation“, forbid a user input from a data within a date range, only outside values from a given date range will be accepted. This is another trouble-free way of creating an xlsx file with data validation using PhpSpreadsheet.

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 date not between a month ago and a month ahead:"
 * string value, will serve as the 'Input Title'.
 */
$sheet->setCellValue('B3', 'Enter a date not between a month ago and a month ahead:');

/**
 * Set the date 2 months ago as the default value for C3.
 *
 * The user will only be allowed to input a date before one month
 * ago or a date after one month from now, and for any invalid input,
 * the date 2 months ago (default) will be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date('m/d/Y', strtotime('-2 month')));

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

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

/**
 * Set the operator to 'Not Between' to
 * check for any date not in range value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_NOTBETWEEN);

/**
 * Set the date one month ago as the 'Start date'
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('-1 month'))));

/**
 * Set the date one month from now as the 'End date'.
 */
$validation->setFormula2(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('+1 month'))));

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

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

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

/**
 * Describe the note.
 */
$validation->setPrompt('Must be not between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');

/**
 * 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 between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');

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

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

create-xlsx-files-with-date-not-in-range-data-validation.php

Test.

Run the following codes.

$ php create-xlsx-files-with-date-not-in-range-data-validation.php

command line

Result.

Open the generated file create-xlsx-files-with-date-not-in-range-data-validation.xlsx.

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

/**
 * Set the date 2 months ago as the default value for C3.
 *
 * The user will only be allowed to input a date before one month
 * ago or a date after one month from now, and for any invalid input,
 * the date 2 months ago (default) will be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date('m/d/Y', strtotime('-2 month')));

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

Check the settings.

1. Click on Data.
2. Click on cell C3.
3. Click on ‘Data Validation’.

The ‘Data Validation‘ window should show up.

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

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

/**
 * Set the operator to 'Not Between' to
 * check for any date not in range value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_NOTBETWEEN);

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 minimum date the user cannot enter (one month ago from the date today).

/**
 * Set the date one month ago as the 'Start date'
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('-1 month'))));

Set the maximum date the user cannot enter (one month in the future from the date today).

/**
 * Set the date one month from now as the 'End date'.
 */
$validation->setFormula2(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('+1 month'))));

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

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

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

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

/**
 * Describe the note.
 */
$validation->setPrompt('Must be not between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');

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 on the date range 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 between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');