Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation: want input restricted to date value or N/A
I would like to use data validation to restrict cell input to either a
valid date after 1/1/08, or "N/A". This is the formula I'm starting with: =IF((D17="N/A"),TRUE,IF(D17DATE(2008,1,1),TRUE,FALSE)) The problem is, if the user types anything in text (such as "Something Else"), then Excel calculates the value of the text string is greater than date(2008,1,1). What do I need to do to allow only dates or the string "N/A"? Thanks, Rachel Garrett |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation: want input restricted to date value or N/A
I think this Data Validation formula will do what you want...
=OR(D17="N/A",AND(ISNUMBER(D17),D17DATE(2008,1,1))) Rick "Rachel Garrett" wrote in message ... I would like to use data validation to restrict cell input to either a valid date after 1/1/08, or "N/A". This is the formula I'm starting with: =IF((D17="N/A"),TRUE,IF(D17DATE(2008,1,1),TRUE,FALSE)) The problem is, if the user types anything in text (such as "Something Else"), then Excel calculates the value of the text string is greater than date(2008,1,1). What do I need to do to allow only dates or the string "N/A"? Thanks, Rachel Garrett |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation: want input restricted to date value or N/A
Thank you, Rick. This works! I didnt't know about ISNUMBER.
--Racel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Input Validation | Excel Discussion (Misc queries) | |||
Data Validation - input message | Excel Discussion (Misc queries) | |||
Data Validation Input Message | Excel Discussion (Misc queries) | |||
Data Validation and Input Mask | Excel Discussion (Misc queries) | |||
Age & Time Restricted Validation dates ... | New Users to Excel |