data validation with EOMONTH function

[spence]

I have the following columns:

C D E
StartDate EndDate ContractEndDate

Columns C and D are user-entered fields; Column E is locked.

In order to minizize data entry, I am using E0MONTH in cell D1 to
autopopulate the end date of the value entered into cell C1: =EOMONTH(C1,0)

Ninty-five percent of the time the value in column D will indeed be the last
day of the month of the date entered in column C. However, in some instances
the contract end date in column E is a mid-month date and in these instances
I want to prevent the value in column D from being later than the value in
column E. (In other words, if ContractEndDate is June 15th and StartDate is
June 1st, I want to prevent EndDate from autofilling June 30th; in these
instances I would like a stop warning reminding my users to overwrite the
formula in D1 and manually enter the correct EndDate.)

I put data validation in D1 requiring a value equal to or less than the
value in E1. The validation works if I manually enter a value in D1
(overwriting the formula) but is ignored when D1 is autopopulated based on
the value of C1. Is there a way I can get both end of month autopopulation
AND data validation? Work arounds are appreciated.

Thanks.

 

[daddylonglegs]

Could you achieve this with just a formula in D1?

=if(EOMONTH(C1,0)<=E1,EOMONTH(C1,0),"ENTER DATE HERE"