< Go Back

Excel DATA VALIDATION using YEAR Function

★ 25 minute read

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.

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 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 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 selected date in cell. The prerequisite for using this function is that cell which we are passing to the function is formatted as a date field. Syntax looks like:

 

=YEAR (date)

 

Now we can put 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 cell range.

 

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

 

You can change text in warning message going to Data Validation, Error Alert tab. In box Error message enter 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 defined condition.

 

Another option in data validation is to create a message that will appear when you select cell which is under data validation. By doing this, you will inform 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 box Input Message just enter the text that will be displayed to 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 date which is in current year (in our case 2018). In this case we will just put YEAR(TODAY()) instead of “2017” in 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())

 

 

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar