Date Validation

  • Thread starter Thread starter ragini
  • Start date Start date
R

ragini

hello everybody,
i am new to excel , and i want to format a particular cell such that it
accepts a date and the date should BE ONLY SATURDAY.
If the date is not saturday a message box should appear and say "please
enter the correct date". Please let me know step by step.As i am not
comfortable using excel.

posted this question yesday and i got a reply =WEEKDAY(A1)= 6

but this is not working .

plz help me guys!!

Thanx,
Ragini.
 
Message boxes are done using visual basic which I wouldn't want to use if I
was a new excel user.

The formula you got yesterday will be most easily put into place if you use
a seperate column. if your dates are in column A, row 1;

if(weekday(a1)<>6,"NOT SATURDAY-PLEASE CORRECT DATE,"")

that will not pop up any boxes, but will let your user know they should
correct the date (you can format the column B with red text, so that the
message really catches their eye!)
 
Assuming it is cell F5 you are looking to restrict, select cell F5, then do Data
/ Validation / Custom / In where it says Formula, put the following:-

=TEXT(F5,"ddd")="Sat"

Then just hit OK.
 
Validation within the cell would require the use of Visual basic incorporated into your worksheet.
If you want to use straight Excel, use a cell adjacent to the date cell to display a warning.
This formula will display "WARNING!" if the date value of A1 is not Saturday, and will be blank if the date value is correct.
=IF(WEEKDAY(A1)=7,"","WARNING!")

HTH
Louise
 
Validation within the cell would require the use of Visual basic incorporated
into your worksheet.

See mine and Peo's answer for a non vba method of doing just that.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Louise said:
Validation within the cell would require the use of Visual basic incorporated into your worksheet.
If you want to use straight Excel, use a cell adjacent to the date cell to display a warning.
This formula will display "WARNING!" if the date value of A1 is not Saturday,
and will be blank if the date value is correct.
 
The best way to do this is as follows,

From the tool bar, slect "DATA"
Then Select "VALIDATION"
Under settings, in the list box labeled
(allow),select "CUSTOM"
Under the list box labeled Formula, type "Saturday" (with
quotes included)

Under the Alert tab,
Choose a "STOP" Message
You do not have to enter a title. Type your cutom error
message in the error message box.
Click on "OK"

Now try out your new cell.
 
Did you try that?


--

Regards,

Peo Sjoblom


Kevin Riley said:
The best way to do this is as follows,

From the tool bar, slect "DATA"
Then Select "VALIDATION"
Under settings, in the list box labeled
(allow),select "CUSTOM"
Under the list box labeled Formula, type "Saturday" (with
quotes included)

Under the Alert tab,
Choose a "STOP" Message
You do not have to enter a title. Type your cutom error
message in the error message box.
Click on "OK"

Now try out your new cell.
 
LOL - And besides which, how could it be the best way, when mine is the best way
<VBG>
 
Back
Top