Go Back

How to Perform Excel Data Validation Using the YEAR Function

Read time: 25 minutes

In this tutorial, we will show how to use DATA VALIDATION to allow entering date only from a certain year. DATA VALIDATION allows you to validate fields that are entered and changed by certain criteria. In order to create this data validation, you will have to use YEAR function.

Data validation in Excel using the YEAR function

Let’s immediately go to the example to present how DATA VALIDATION for entering date only from a certain year works. We will restrict data entrance and data change in Column Training date (C3:C7) only to the year 2017:

 

Click on the data tab and under section Data Tools choose Data Validation. In tab Settings, part Allow, choose Custom. To restrict data entrance only to the 2017 year enter the Formula:

=YEAR(C3) = 2018

To be able to understand data validation, we first need to go through YEAR function and explain how it works. This function returns the year from the selected date in the cell. The prerequisite for using this function is that cell which we are passing to the function is formatted as a date field. The syntax looks like:

=YEAR (date)

Now we can put a data validation condition in cell C3. In order to expand it to column “Training date” just select relevant cell range and click Data Validation under Data tab, section Data Tools. You will be asked to expand Data Validation to a cell range.

 

If we try to enter a date which is not in the 2017 year, an error message will appear as in the example below:

 

You can change the text in warning message going to Data Validation, Error Alert tab. In the box, Error message enter a warning message and choose Style Stop. It is important to use style Stop since this style will allow you to enter data only with conditions defined in previous steps. If you choose style Information or Warning, you will be allowed to enter data that does not meet the defined condition.

 

Another option in data validation is to create a message that will appear when you select a cell which is under data validation. By doing this, you will inform the user that cell which he/she selected has data validation with description message. You can enter this message under Data Validation, Input message tab. In the box, Input Message just enter the text that will be displayed to the user:

Now when user select cell that is under data validation, he/she will see the message about data validation:

 

Instead of setting validation on a certain year, we could also set a validation to allow only a date which is in the current year (in our case 2018). In this case, we will just put YEAR(TODAY()) instead of “2017” in the previous example. By doing this, TODAY() will return today (current) date and YEAR function will return current year for today date:

=YEAR(C3) = YEAR(TODAY())

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Trying to pull data from ONLY a certain date into the table. Have it sorted now but it picks all dates. thanks in advance,
Solved by B. Q. in 12 mins
I have created a worksheet but need to allow people to only print certain groups of cells based on a checkbox selection.
Solved by I. H. in 24 mins
One column has lease expiry dates, I only want to include data from leases that are greater than a certain time period in a pivot table, can I do that?
Solved by O. A. in 29 mins

Leave a Comment

avatar