Validate test for weekends and holidays

T

taoist

New Member
Joined
Jan 26, 2005
Messages
15
Working on this spreadsheet at work. Trying to figure out how to validate user input. Want to warn user if date being entered is a saturday, sunday or defined list of holidays. Also want to prevent these days from being entered. Any other day is OK.

Would appreciate any help and be specific. I have had no luck in creating tables and such. I must be doing something wrong. I am still getting up to speed on Excel's finer points.

taoist :confused:
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
If you want to prevent weekends or holidays being put into A1 use Data > Validation > custom with the format

=AND(WEEKDAY(A1,2)<6,ISNA(MATCH(A1,$Z$1:$Z$10,0)))

where Z1:Z10 contains your holiday dates
 
Upvote 0
You can use Data Validation - CUSTOM

enter this formula

=AND(COUNTIF($C$1:$C$10,A1)=0,WEEKDAY(A1)<>7,WEEKDAY(A1)<>1)

Where A1 is the cell you enter the data validation into and
$C$1:$C$10 is the range holding your holidays.
 
Upvote 0
Thanks very much both of you! The formulas work perfectly!

I really appreciate everyones response to my questions.

This is a great message board.

taoist :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
304
Romy
R
C
  • Question
Finding weekends and holidays within a date range
Replies
1
Views
350
ColinPearsonEIT
C
C
  • Solved
NETWORKDAYS.INTL holiday question
Replies
9
Views
271
Fluff
Fluff
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
280
Fluff
Fluff
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
1K
felixstraube
F

Forum statistics

Threads
1,215,315
Messages
6,124,213
Members
449,148
Latest member
sweetkt327

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top