Data validation date in specific year

How to check for Data validation date in the specific year

Just as it is with using Excel to solve many things, you can also use it to check Data validation of date in a specific year. This post will guide you on how to create Data validation date in a specific year using a formula approach in Excel.

The general formula to check for Data validation date in a specific year is:

=YEAR(Date)=2018

Explanation

If you want users to only enter dates in a certain year, you can employ data validation formula based on the YEAR function.

Example

Figure1. Data validation in a specific year

In figure 1 above, the data validation applied to D4:D7 is:

= YEAR(D4)=2018

How the formula works

The data validation rules are activated when a user changes or add a new cell value. The validation formula tests the year of any given date against the value of a hard-coded year using the YEAR function.

When a user inputs a date, the function extracts the year and compares it to 2016. If they correspond, the expression returns TRUE and validation becomes successful. If on the contrary, the expression returns FALSE and the data validation fails.

Date in the current year

To allow only dates in the current year, you can modify the formula thus:

YEAR (D4)=YEAR(TODAY())

The “TODAY” function returns the current date, which is recalculated on a regular basis. The expression, therefore, returns TRUE only when a date is in the current year.

Useful note

  • Excel features many integrated data validation rules for dates.
  • With this function, you can develop a personalized validation rule if you want more flexibility and control.
  • Cell references in data validation formulas are determined by the upper left cell in the selected range once the validation rule is specified. In this scenario, it is D4.
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

Leave a Comment

avatar