Date Range Question

[Rodman]

Assume there is a date range from 1/1 to 3/31 (i.e., first quarter).

In cell A2 I have a start date, in cell B2 I have an end date.

If the start date is greater than the end date
or the end date is less than the start date
or the dates are not within the date range
display "Date Error" in cell C3

Can someone help with a macro or formula for this problem?

Thanks in Advance

 

[Jim Cone]

Dates in A2:A91
Start Date in B2
End Date in C2
Message formula in D2
Min function for dates in E2
Max function for dates in F2

Formula in D2...
=IF(OR(B2< E2,B2>F2,B2>C2),"Start Date Error",IF(OR(C2<E2,C2>F2,C2<B2),"End Date Error","OK"))
--
Jim Cone
Portland, Oregon USA



"Rodman" <[email protected]>
wrote in message
Assume there is a date range from 1/1 to 3/31 (i.e., first quarter).
In cell A2 I have a start date, in cell B2 I have an end date.

If the start date is greater than the end date
or the end date is less than the start date
or the dates are not within the date range
display "Date Error" in cell C3

Can someone help with a macro or formula for this problem?
Thanks in Advance

 

[Rodman]

Thanks! - This solution worked great!

now I have been asked to put the error message in the offending cell.

ex if cell A2 is outside of the date range the error message placed in cell
A2 coule be "#DER (for date error).

Thanks In Advance

 

[Rodman]

Not Exactly...

In the sample being discussed if there was an error (i.e., the data not
within the range) an error would be displayed in cell C2. I would like to
put the error in the cell in which the error occurs. If, for example, the
date in cell A2 is not within the date range I would like to put the error
message #DER in that cell.

Additionally, I would like to make sure the start date is not greater than
the end date and the end date is not less than the start date (for that date
range). I think this will have to be a macro.

I have sucessfully used your example to make sure the dates are within thr
range but have not figured out how to determine the greater- than, less-than
date issue.

Regards,