entry must match value in range
 

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.

entry must match value in range
 

Data Validation Custom Formula:

=COUNTIF(B1:IV1,A1)0
--
Gary''s Student - gsnu200788

entry must match value in range
 

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" wrote in message
...
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.

entry must match value in range
 

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)" wrote:

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" wrote in message
...
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.

entry must match value in range
 

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" wrote in message
...
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)" wrote:

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" wrote in message
...
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.

entry must match value in range
 

Success! Thank you, Rick.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)" wrote:

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" wrote in message
...
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.