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:
If you want users to only enter dates in a certain year, you can employ data validation formula based on the YEAR function.
Figure1. Data validation in a specific year
In figure 1 above, the data validation applied to D4:D7 is:
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:
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.
- 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.