Explanation
Data validation rules are triggered when a user adds or changes a cell value.
This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function.
When a user enters a value, the YEAR function extracts and compares the year to 2016:
=YEAR(C5)=2016
When the years match, the expression returns TRUE and validation succeeds. If the years don't match, or if the YEAR function is not able to extract a year, validation fails.
Date this year
To allow only dates in the current year, you can adjust the formula like this:
=YEAR(C5)=YEAR(TODAY())
The TODAY function will return the current date on an on-going basis, so formula returns TRUE only when a date is in the current year.
Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.
Related formulas
Data validation allow numbers only
Data validation allow text only
Data validation only dates between
Data validation date in next 30 days
Related functions
YEAR Function
The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.
TODAY Function
The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY with a date number format. If you need current date and time, use...
