Using Date Function in Data Validation in Excel

Data validation is the process of ensuring the accuracy and quality of data in Excel. In this article, I will walk you through how to use the DATE Function in Excel to restrict invalid data to a dataset. 

The DATE function returns the sequential serial number that represents a particular date.

Syntax

DATE(year,month,day)

The DATE function syntax has the following arguments.

• Year: Required. The value of the year argument can include one to four digits. Excel interprets the year argument according to the date system your computer is using. By default, Microsoft Excel for Windows uses the 1900 date system, which means the first date is January 1, 1900.
• Month: Required. A positive or negative integer representing the month of the year from 1 to 12 (January to December).
• Day: Required. A positive or negative integer representing the day of the month from 1 to 31.

In the screenshot below, we have sample data with a few columns. We want to apply Date Data Validation criteria to the Date column so that dates between 01/01/2015 to 31/12/2022 are accepted as valid entries.

To apply the data validation, we have to select the data from cell A2 to the last non-blank cell below.

Next, in the Data Tools group of the Data tab, click on Data Validation.

In the Data Validation dialogue box, select Data from the drop-down, and we want to perform between data operations.

In the Start Date, enter the formula.

=DATE(2015,1,1)

In the End Date, enter the formula.

=DATE(2015,1,1)

In the Input Message and Error Alert tabs, we provided a Valid Date Entry and the message, "Enter dates between 01/01/2015 and 31/12/2022," as seen in the screenshot below.

Click OK

To test the validation criteria whether it is working or not, I entered the 01/01/2023 date in cell A2, which is outside the Validation criteria, and voila, we discovered that the new date is not accepted, as seen in the screenshot below.

 

See you in the next article.