Excel formula - find if any date in a given range matches a given year

Question

I have a table listing work anniversaries, looking something like:

 5 years   | 10 years  | 15 years  | 20 years... etc
 27/1/2011 | 27/1/2016 | 27/1/2021 | 27/1/2026.. etc

I would like to check if any of the dates falls within the current year. I can do it easily enough with a formula such as =OR(year(cell1)=2021,year(cell2)=2021.. etc but it's not particularly elegant. What would be a better way to do it?

question from:https://stackoverflow.com/questions/65910358/excel-formula-find-if-any-date-in-a-given-range-matches-a-given-year

Answer 1

Use COUNTIFS:

=COUNTIFS(A2:F2,">="&DATE(2021,1,1),A2:F2,"<"&DATE(2022,1,1))>0

A2:F2 being the dates to test