Prevent a Weekend date entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column used to insert dates for a certain activity. Is is possible
for me to restrict someone from entering a date that falls on a specific day
of the week?

Thanks in advance
Anurag
 
I have a column used to insert dates for a certain activity. Is is possible
for me to restrict someone from entering a date that falls on a specific
day
of the week?
Click to expand...

If we are talking about a user typing in a date, you could use Excel's
Data/Validation menu item to do that. For example, to restrict the user from
entering a date that falls on a Wednesday, you could select the entire
column and then use this Custom Formula in the Data/Validation dialog box...

=WEEKDAY(A1)<>4

where I have assumed the dates are in Column A.

Rick
 
That worked. I had tried using the same formula earlier without actually
selecting the entire column and that didnt work for me.

Since I wanted the weekend, I tied to use this formula:
=OR(WEEKDAY(D1)<>1,WEEKDAY(D1)<>7)

But with this, nothing is working!
 
You don't want OR for that combination... you want AND

=AND(WEEKDAY(D1)<>1,WEEKDAY(D1)<>7)

Both must be true for the date to be on Monday thru Friday; however, by
using the optional 2nd argument for the WEEKDAY function, you can use a
simple formula like the original one I posted. Try this custom validation
formula instead (note the 2 in the second argument of the WEEKDAY function
call)...

=WEEKDAY(D1,2)<6

Rick
 
Nevermind. I got it. Many thanks.

Rick Rothstein (MVP - VB) said:
If we are talking about a user typing in a date, you could use Excel's
Data/Validation menu item to do that. For example, to restrict the user from
entering a date that falls on a Wednesday, you could select the entire
column and then use this Custom Formula in the Data/Validation dialog box...

=WEEKDAY(A1)<>4

where I have assumed the dates are in Column A.

Rick
Click to expand...
 
Back
Top