Validate cell for date, as well as day of the week

External Since: Sep 03, 2007 Posts: 3

I have looked through what is out there and I am not hopeful that this can be
done, but here goes...

I have a cell that will be populated with a date. I need to make sure that
the date does not fall on a Saturday or a Sunday and that the date is not
older than 2 days. I was hoping to be able to do both of these in a single
validation, but I have a snag. The weekday function requires a cell, but I
am validating on the current cell and, even though I do know what it is, i
want to allow that that may change. How can I accomplish this?

External Since: Nov 22, 2006 Posts: 251

David

I could not get data validation to work. But until someone answers your
query perhaps you can use conditional formatting.

Set the cell condition to; Formula Is and paste the formula

=OR(B1<TODAY()-2,WEEKDAY(B1)=1,WEEKDAY(B1)=7,B1>TODAY())

and set the colour to say to red.

You can set this over a range - just type the reference to the first cell in
the range. The conditional formatting can be copied anywhere using the Paste
Format icon.

Peter Atherton

"David Langschied" wrote:

> I have looked through what is out there and I am not hopeful that this can be
> done, but here goes...
>
> I have a cell that will be populated with a date. I need to make sure that
> the date does not fall on a Saturday or a Sunday and that the date is not
> older than 2 days. I was hoping to be able to do both of these in a single
> validation, but I have a snag. The weekday function requires a cell, but I
> am validating on the current cell and, even though I do know what it is, i
> want to allow that that may change. How can I accomplish this?
>
>
>