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]

Why not do this instead?

 

[Jazz Engineer]

Why not do this instead? View attachment 73971

Why not do this instead? View attachment 73971

 

[Jazz Engineer]

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.

Also, something doesn't make sense to me: I can't imagine a program as sophisticated as Excel not supporting nested functions anywhere. I fear that I've played around with a setting in Options that would explain why this isn't working. In support of this hunch, YSKT this worked for a while. While the top cell in the range wouldn't trigger an error, subsequent cells below would.

 

[StephenCrump]

You're testing: >=YEAR(TODAY())

Which means >=2022, i.e. >=14 July 1905.

Instead, try: >=DATE(YEAR(TODAY(),1,1)

 

[Phuoc]

Or try

=YEAR(A5)>=YEAR(TODAY())

 

[Jazz Engineer]

You're testing: >=YEAR(TODAY())

Which means >=2022, i.e. >=14 July 1905.

Instead, try: >=DATE(YEAR(TODAY(),1,1)

When I tried your suggestion, I received this error message:

Did you test your idea before responding to me? I still fear that I have a setting somewhere in options that is affecting this matter. Thanks anyway!

 

[Jazz Engineer]

Or try

=YEAR(A5)>=YEAR(TODAY())

This one produced interesting results. I entered your suggestion as evidenced by the screen capture below:

But I also copied the formula into cell A8. It would return FALSE/TRUE depending on the date entered in A5. This is what happened when I entered the date 7/14/21 (Yes, the cells are formatted as Date). Also, the value in A8 was FALSE.

However, when I entered a date that should've passed muster, this is what happened:

The date is valid for the restriction, the error message says it isn't valid, but the formula in A8 says it is.

Any ideas on what's happening?

Steve

 

[StephenCrump]

When I tried your suggestion, I received this error message ...

That's a VBA message box. You must have VBA code applying its own checks?

 

[StephenCrump]

This one produced interesting results. I entered your suggestion as evidenced by the screen capture below ....

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