I need to create a data validation menu from a range like B:B where the entire column is populated with date and time every 2 hours. I need the menu to output as options only the years without repetition in this case only 2021 and 2022 but when dates from 2023 start being added automatically 2023 is added to the validation menu. Here is a dummy file to exemplify:

CodePudding user response：

This worked on A1 or wherever then point data validation to A:A or etc:

=SORT(UNIQUE(ARRAYFORMULA(IF(ISBLANK(B4:B),,YEAR(B4:B)))))