Date Validation - Must equal Sundays date

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

Guest

I have a nifty little time sheet for 3 employees to use. Unfortunatly this
thing needs to be dummy proof because now I am responsible of making sure
they enter the right dates. All they need to do is enter every other weeks
start date.
For instance:
John's pay period starts on 12/4/05 (a sunday). All he has to do is type in
12/4/05 in one cell and the spreadsheet fills in the end date, the weekday
dates, etc..
He won't have to do it again until 2 weeks (12/18/05).
John can't seem to enter the right date and then payroll gets mad and they
yell at me...blah blah.
So I tried doing a data validation for a range of cells (I have all the
valid Sundays in a column on the same sheet). What am I doing wrong??
Is this even possible?
 
Use data>validation>custom and in the formula box put

=WEEKDAY(A1,2)=7

where A1 is the cell with the validation, click the error alert tab and type
a message like "John, learn the difference between Sunday and other days!"
 
I used that code and in the formula except it says:
=WEEKDAY(U1,2)=7
In cell U1 I have 12/04/05

Is this correct? I enter 12/06/05 and it take it and every other date I try.
I must be missing something.
Also: U1 = the cell, what does the ",2" do for the formula?

I put that message in there too! Maybe he'll get the picture!

Thanks !
 
Take a look at Excel's help for =weekday(). It has a nice explanation about
what that number means.

When you applied data|Validation, did you check the "show error alert..." box on
the Error Alert tab?

Make sure you make the Style "stop", too.
 
Style is on stop, error message is there

Dave Peterson said:
Take a look at Excel's help for =weekday(). It has a nice explanation about
what that number means.

When you applied data|Validation, did you check the "show error alert..." box on
the Error Alert tab?

Make sure you make the Style "stop", too.
Click to expand...
 
Tried it on a new spreadhseet...to make sure my other sheet wasn't messed up
and the same result happened....nothing.
 
I'd guess you made a mistake when you typed in the formula.

I'd try again.

And make sure you type in the month/day/year in the correct order.

June 12, 2005 is a Sunday.

(Although, April 12, 2005 is not.)

I'm still guessing typo.
 
What cell was active when you applied Data|Validation?
What was the formula you used?

Copy and paste from that dialog.
 
A1 is 12/04/05
Highlight Cell C1.
I went to data/validation
changed to custom and entered the formula =WEEKDAY(A1,2)=7
Put a Stop message on

Now when I type any date (12/07/05 - - month, day, year) it lets me with no
warning or stop. I tried 5 different dates.
I retyped and even did a copy past of the formula

Office 2003 SP2 by the way
 
Are you using the same cell, i.e. do you select U1, then do
data>validation>allow>custom and put in the formula

=WEEKDAY(U1,2)=7


--

Regards,

Peo Sjoblom
 
If you're typing in A1, then select A1 and apply data|validation.

If you're using A1 to stop entry in C1, then as long as A1 is a Sunday, you can
type anything you want in C1.

Change A1 to a Saturday and you'll be stopped in C1.


A1 is 12/04/05
Highlight Cell C1.
I went to data/validation
changed to custom and entered the formula =WEEKDAY(A1,2)=7
Put a Stop message on

Now when I type any date (12/07/05 - - month, day, year) it lets me with no
warning or stop. I tried 5 different dates.
I retyped and even did a copy past of the formula

Office 2003 SP2 by the way
Click to expand...
 
A1 is 12/04/05
Highlight Cell C1.
I went to data/validation
changed to custom and entered the formula =WEEKDAY(A1,2)=7
Put a Stop message on

Now when I type any date (12/07/05 - - month, day, year) it lets me with no
warning or stop. I tried 5 different dates.
I retyped and even did a copy paste of the formula
C1 is the one I want to type in and get a stop message.
 
Select A1, not C1, you need apply validation to the cells that you enter the
dates in

--

Regards,

Peo Sjoblom


jeridbohmann said:
A1 is 12/04/05
Highlight Cell C1.
I went to data/validation
changed to custom and entered the formula =WEEKDAY(A1,2)=7
Put a Stop message on

Now when I type any date (12/07/05 - - month, day, year) it lets me with no
warning or stop. I tried 5 different dates.
I retyped and even did a copy past of the formula

Office 2003 SP2 by the way
Click to expand...
 
Bingo! I am a total retard!
What I thought was A1 was referring to the start date to use.
I am sorry...long day, brain isn't working properly I guess. Thank you and
Thank you Dave!!!
 
Sorry guys...kicking myself here for not taking a deep breath and going
slow...misread it. Thank yo uso much for your patience!
 
Back
Top