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

[David Langschied]

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?

 

[Billy Liddel]

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

 

[OssieMac]

Use a formula (custom) in data validation and insert the following formula:

Replace A1 with the first cell in the selected cells to apply the validation
to.

=AND(A1>=TODAY()-2,TEXT(A1,"ddd")<>"Sat",TEXT(A1,"ddd")<>"Sun")

or you could weekday function in lieu of text function but I like the above
because it is self documenting as to which days to exclude.

 

[Shane Devenshire]

Hi,

In the data validation Custom area enter the formula

=AND(MOD(A1,7)>1,(TODAY()-A1)<2)

Note you specified a date not more than two days old, but you did not
indicate if that date could be in the future.

If this helps, please click the Yes button

Cheers,
Shane Devenshire