Data validation with date function fails

[Jazz Engineer]

I have a column that is supposed to have dates entered, and is formatted for short date. The condition is: any date entered must have occurred in this year or a later year. I have entered the XL2BB code below and a screen shot afterwards. The problem is that dates in previous years are accepted without any error messages.

e04Marathons_CarmeliSteve.xlsm
	A
5	3/4/2020
Expenses

Cells with Data Validation
Cell	Allow	Criteria
A5	Date	>=YEAR(TODAY())
B5	List	=Marathon_Name
A6:A40	Date	<TODAY()
B6:B12	List	Marathons
B13:B40	List	=Marathons

Thanks for your help,

Steve

 

[rlv01]

rlv01

That's not a bad solution, but it's not perfect. Suppose I want to convert my workbook to a template? This would enable users to enter event data from 2022 in 2023.

If you are looking for something to reset the earliest valid date to the first day of the current year, then something like this.

Coincidentally, it is also an example of using nested formulas.

 

[Jazz Engineer]

This approach uses a formula, rather than testing values, hence it needs to be applied like this:

View attachment 74326

Mr. Crump,

This solved the problem! Thanks.

Steve / Jazz Engineer

 

[StephenCrump]

Thanks for posting back. I'm glad we could help.