Data Validation Query
Posted by JAF on June 04, 2001 4:03 AM
Hi - I'm not sure if this is possible, but here we go...
I've been using the Data Validation settings to ensure that a specified column can only contain date values greater than or equal to 01/01/2001.
I now need to allow users to input either a valid date OR the text "N/A" - obviously, using the Data Validation set to "Date" prevents me from inputting "N/A" into the column.
Is there any way to force dates in the column to be valid dates (i.e. to stop careless people from inputting something like 31st April 2001), but to still allow the use of N/A as well?
Any suggestions???
Posted by Eric on June 04, 2001 8:10 AM
Use a list instead for validation
You could make a list of the dates that are useful and include the term "N/A" in the list, then use that list for validation
Posted by JAF on June 04, 2001 11:10 PM
Re: Use a list instead for validation
Nice idea, but not really feasible.
The user can input any date greater than or equal to 01/01/2001, so the "list" will very quickly become VERY big.
Posted by Eric on June 05, 2001 7:35 AM
Sorry, didnt appreciate the scale of your problem
I was thinking you could make a list of the dates in a 65,536 row column but that would only give you about 179 years worth of dates, are you doing mortgages or something?
Posted by Aladin Akyurek on June 05, 2001 12:29 PM
Until you find some VBA code somewhere... ;)
Put the criterion date in some cell and date format it as desired. Name this cell CDATE (for Criterion Date) via the Name Box.
Activate an empty cell where the date by user must be entered. Activate then Data|Validation. On Settings Dialog, choose Custom for "Allow" and enter the following formula.
=OR(AND(ISNUMBER(A1),A1>=CDATE+0,CELL("format",A1)=CELL("format",CDATE)),AND(ISTEXT(A1),A1="N/A"))
Keep Ignore Blank checked.
Do not format the cells where dates are to be entered, except for Center, Left Align or Right Align.
Copy down the empty cell (that now contains a data validation formula) as far as desired.
Hope this helps.
Aladin
=====================
Posted by Tim Francis-Wright on June 05, 2001 2:18 PM
You might be able to use a Custom validation
like the following:
=(B8="N/A")+AND(ISNUMBER(B8),IF(ISNUMBER(B8),AND(B8>0,B8=INT(B8))))
I think that this will do the trick. It has
the advantage of not needing a separate
criterion range.
You could substitute B8>0 for AND(B8>0,B8=INT(B8))
if you didn't mind times other than midnight.
(You can also include a maximum and minimum date;
I would use DATEVALUE for less brain damage
later on.)
