entry must match value in range

[JayBro]

I have a range of cells with dates like: 1/1; 1/15; 1/31; 2/1; 2/15; 2/28...

To the left of the range a user will enter a date. I want to make sure that
the date matches a value in the range above. For example, 1/7 is invalid
whereas 2/1 is fine. I'd prefer not to use a dropdown. This hasn't been easy
for me to figure out and I'm hoping someone can help me instert the correct
validate formula.

 

[Gary''s Student]

Data > Validation > Custom > Formula:

=COUNTIF(B1:IV1,A1)>0

 

[Rick Rothstein \(MVP - VB\)]

Is the column you are filling in formatted as Date with a display format of
m/d? If so, you can delete your range of approved dates and use this
Validation formula...

=OR(DAY(A1)=1,DAY(A1)=15,DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))

Note: This formula will use February 29th (instead of the 28th) as the valid
end of February date in a leap year (such as this year).

Rick

 

[JayBro]

I'm still not able to make it work. I'm using Excel 2007. Under Data | Data
Validation I'm usign the criteria "Date, "equal to" and entering Rick's
formula. The result is that nothing appears to be valid. I tried the COUNTIF
suggestion too matching to a series of existing dates but get the same
behavior. Can someone steer me in the right direction? Maybe it's back to the
drawing board on Data Validation, I don't know.

 

[Rick Rothstein \(MVP - VB\)]

The result from my formula is not a date (it produces a logical TRUE or
FALSE result), so you can't validate it as one. Instead of Date, select
Custom from the Validation Criteria's Allow drop-down field and then put my
formula in the Formula field.

Rick

 

[JayBro]

Success! Thank you, Rick.

The result from my formula is not a date (it produces a logical TRUE or
FALSE result), so you can't validate it as one. Instead of Date, select
Custom from the Validation Criteria's Allow drop-down field and then put my
formula in the Formula field.

Rick