Data Validation - 1st or 16th of the month only

J

John

In a certain cell the user enters a date. I would like data validation to
accept the entry only if the day is the 1st or 16th of the month. How do I
do that?
 
G

Gary''s Student

Use Formula is:

=OR((DAY(F1)=1),(DAY(F1)=16))

for the validation rule in F1
 
R

Ron Coderre

Select the range of cells to be impacted

Example with A2:A10...with A2 as the active cell
From the Excel Main Menu:
<data><validation>
Settings Tab:
....Allow: Custom
....Formula: =AND(A2>30000,OR(DAY(A2)=1,DAY(A2)=16))
Error Alert Tab:
....Title: Invalid Entry
....Error message: Date must be the 1st or 16th of the month.
Click: OK

Note: I only used 30000 since most recent
dates are larger than 30000
(which happens to be 18-Feb-1982)

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)
 
Top