Dependent date validation

[numb7]

Hello Smartest Excellers in the World,

I am trying to use data validation to restrict date values entered into a cell. I am not able to use macros to accomplish this because the form that I am creating will be distributed to many users (who are not likely to have macros enabled).

Essentially, I am trying to restrict values in this way:
IF (A1="future",[allow values greater than today], IF (A1="past",[allow values less than today], [allow whatever]))

I have attempted to use the following formula as a Custom restriction in Excel's Data Validation tool:
=if($d5="future",>today(),if($d5="retroactive",<today(),""))

Excel tells me that this is not a valid formula. Ideally, I do not want to create name ranges of dynamic dates and provide a drop down list to users; I want the users to be able to type a date and be stopped if the date does not meet my criteria.

Any thoughts on how I can accomplish this are greatly appreciated.</today(),""))

 

[AlphaFrog]

Data Validation setting:

Allow: Date
Data: Between
Start Date: =IF(A1="Future",TODAY(),0)
End Date: =IF(A1="Past",TODAY(),99999)

 

[numb7]

That is so simplistically perfect. Thanks, AlphaFrog!