Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

How not allow entry of weekend dates or holiday dates

How can I disallow the entry of a weekend date or holiday date in a particular cell?  I do have a named range named "Holidays" with the dates in place.

--Steve
0
SteveL13
Asked:
SteveL13
1 Solution
 
PhoenixkeCommented:
0
 
SteveL13Author Commented:
Although I found this somewhat helpful it didn't really provide a solution to my question unless I'm missing something.
0
 
OP_ZaharinCommented:
there is another excel question regarding date restriction in this portal:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25784324.html

you can download the sample excel file and change the code to suits your date selection needs.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
McOzCommented:
Steve, use custom data validation with a formula like this:

=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)))

Where E1:E6 is your holidays range. For some reason I couldn't get it working with a named range, but maybe you will find a way!

Cheers
0
 
barry houdiniCommented:
Which version of Excel are you using, Steve?

If you have Excel 2007 or later you can use data validation - Assuming you want to restrict cell E2 to workdays choose "custom" option and enter this formula

=WORKDAY(E2+1,-1,Holidays)=E2

If the cell is currently empty you'll get a warning that the formula evaluates to an error - that's OK, you can ignore that.

It's possible in earlier versions but you'll need a more complex formula.....

regards, barry
0
 
barry houdiniCommented:
Actually McOz, I got the same thing - couldn't make it work with the named range but it works with a specific range as long as that range is on the same sheet, e.g.

=WORKDAY(E2+1,-1,H$1:H$10)=E2

where holidays are in H1:H10

In Excel 2003 or earlier you can use this version

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

If you have the holiday range on another sheet you can make it work with INDIRECT function, e.g. if holidays are in H1:H10 on a different sheet [sheet2] you can use this version

=AND(WEEKDAY(E2,2)<6,COUNTIF(INDIRECT("Sheet2!H1:H10"),E2)=0)

regards, barry
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now