Data Validation with Blank Date

[Gos-C]

Hi,

I have the following formula in cell *CP2* that evaluates to blank
(""):

=IF(BU2+7<DATE(YEAR(DATE!E23),MONTH(B2)+1,0),BU2+7,IF(AND(BU2+7=DATE(YEAR(DATE!E23),MONTH(B2)+1,0),CN2<DATE(YEAR(DATE!E23),MONTH(B2)+1,0)),DATE(YEAR(DATE!E23),MONTH(B2)+1,0),IF(AND(BU2+7>DATE(YEAR(DATE!E23),MONTH(B2)+1,0),CN2<DATE(YEAR(DATE!E23),MONTH(B2)+1,0)),DATE(YEAR(DATE!E23),MONTH(B2)+1,0),"")))

BU2[/B] CONTAINS A FORMULA THAT EVALUATES TO *28-07-2006

DATE(YEAR(DATE!E23),MONTH(B2)+1,0)* EVALUATES TO *31-07-2006

B2* CONTAINS FORMULA THAT EVALUATES TO *JULY

CN2* CONTAINS FORMULA THAT EVALUATES TO *31-07-2006


When I set the Validation citeria* to *Custom*, and entered the
following formula:

=OR(AND(CK5>=DAY($CN$2),CK5<=DAY($CP$2)),AND(CK5=DAY($CN$2),$CP$2
=""))

I get the message "The formula currently evaluates to an error. Do you
which to continue?"

When I choose "Yes," it does not accept *31* in cell CK5, (but when I
delete the formula in cell CP2 - leaving the cell blank, it accepts 31
only)

I know that the problem is being caused by the blank ("") in cell CP2.
Any help in fixing that problem?

Thanks,
Gos-C

 

[Gos-C]

I have been able to solve this one yet, so let me try to explain some
more.

Cell CN2 will contain the date of the last Monday of the month –
formatted as d. Cell CP2 will either contain the EOM date (if EOM
falls on Tuesday, Wednesday, Thursday or Friday) or blank (if EOM also
falls on the last Monday). For example:

For May 2006:
Cell CN2 will contain 29
Cell CP2 will contain 31
So with the data validation setting, only 29, 30 or 31 should be
allowed in cell CK5

For June 2006:
Cell CN2 will contain 26
Cell CP2 will contain 30
So with the data validation setting, only 26, 27, 28, 29 or 30 should
be allowed in cell CK5

For July 2006:
Cell CN2 will contain 31
Cell CP2 will be blank
So with the data validation setting, only 31 should be allowed in cell
CK5

Hope this helps

Gos-C

 

[Gos-C]

I have posted more explanation. Can anyone help?

Thank you,
Gos-