Firing Blanks

  • Thread starter Thread starter mlv
  • Start date Start date
M

mlv

Excel 2002 SP3

I have a cell in a worksheet that uses a formula to automatically insert a
date. The cell is formatted 'Date 14/03/2001'

Formula:

=IF(DATE(YEAR(Setup!E8)+1,MONTH(Setup!E8),DAY(Setup!E8)-1)>DATE(YEAR('Month
12-ML'!N3),MONTH('Month
12-ML'!N3)+1,0),DATE(YEAR(Setup!E8)+1,MONTH(Setup!E8),DAY(Setup!E8)-1),"")

When the formula criterion is not met, the formula will leave the cell empty
(by entering "").

In exceptional circumstances, the user is allowed to overwrite the formula
and directly enter a date.

Because the user is allowed to enter a date manually, I set Data Validation
to 'Date' and ticked 'Ignore Blank', to stop erroneous data being entered.

Unfortunately Data Validation does not see "" as a blank, and consequently
my formula fails when it tries to enter "".

Is there a way around this, other than by disabling Data Validation? Can I
create a Custom Data Validation that will allow both a date and ""?

What exactly is a 'Blank' entry anyway, and can a formula be made to enter a
true blank, rather than ""?

Thanks
 
The NA() function could be used in place of "". Alternatively, you may wish
to write a script to validate this cell - to allow both Dates and Blanks.
 
Try in Data Validation select Custom and then enter the formula:

=AND(A1<>"",A1>DATE(2008,1,1),A1<DATE(2008,12,31))

changing the dates to dates of you choice.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy said:
Try in Data Validation, select Custom and then enter the formula:

=AND(A1<>"",A1>DATE(2008,1,1),A1<DATE(2008,12,31))

changing the dates to dates of your choice.
Click to expand...

Hi Sandy

Thanks for the guidance. Once I'd seen how to construct the formula, I
managed to take Data Validation a step further and restrict the manually
entered date to a maximum of one month beyond the last day of the month of a
variable control date that is stored in cell Z1.

Formula:

=AND(A1<>"",A1>DATE(YEAR(Z1),MONTH(Z1)+1,0),A1<=DATE(YEAR(Z1),MONTH(Z1)+2,0))

Excellent result!
 
Castell said:
The NA() function could be used in place of "". Alternatively, you
may wish to write a script to validate this cell - to allow both
Dates and Blanks.
Click to expand...

Thanks Castell.

Unfortunately the NA() function didn't seem to solve the problem as I need
the cell to either carry a valid date, or to be blank.
 
Glad that it helped. Thanks for the feedback

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top