Need additional help with data validation

Someone helped me earlier with my question regarding disallowing weekend or holiday dates into a cell.  Here was the formula...

=AND(WEEKDAY(A1)<>1,WEEKDAY(A1)<>7,ISERROR(MATCH(DATE(DAY(A1),MONTH(A1),YEAR(TODAY())),DATE(DAY(E1:E6),MONTH(E1:E6),YEAR(TODAY())),0)))

But now my problem is that I enter this in data validation and it works fine until I save the file and re-open the file.  Once this has been done the data validation no longer stays in place.

????
SteveL13Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Hello Steve,

Did you try my suggestion?

=AND(WEEKDAY(E2,2)<6,COUNTIF(H$1:H$10,E2)=0)

where H1:H10 is the holiday range - I don't get any problem when I shut it down - which version of excel are you using?

barry
0
 
jppintoCommented:
What to you mean by " the data validation no longer stays in place."?
0
 
barry houdiniCommented:
Here's a sheet with that working - does it work for you?

barry
26893791.xlsx
0
 
barry houdiniCommented:
and in 2003 format if you need that....

barry
26893791v2.xls
0
 
SteveL13Author Commented:
This seems to be working.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.