Excel Data validation date in specific year

Using the example below, this tutorial shows how to create  Data validation date in specific year in Excel.




Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula in case you want or need more control and flexibility.

To allow a user to enter only dates in a certain year, you can use data validation with a custom formula based on the YEAR function.

Also See:   Invoice status with nested if in Excel

In the example shown, the data validation applied to C5:C7 is:


How this formula works

Data validation rules are triggered when a user adds or changes a cell value.

This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function.

When a user enters a value, the YEAR function extracts and compares the year to 2016:


When the years match, the expression returns TRUE and validation succeeds. If the years don’t match, or if the YEAR function is not able to extract a year, validation fails.

Also See:   Highlight dates in the next N days in Excel

Date this year

To allow only dates in the current year, you can adjust the formula like this:


The TODAY function will return the current date on an on-going basis, so formula returns TRUE only when a date is in the current year.

Note:  Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.

Also See:   How to enter Today's Date or Static Date and Time in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *