displaying error message if end date is prior to begin date

[stef]

Excel 2002 SP3
Win XP HE

Hi,

1) In cell B3 I have a beginning date. In B4, an ending date.

The dates are selected from a dropdown list Form I created which uses a
list of dates in a different sheet (column--named as range).

How can I display an error message if the ending date is equal to or
prior to beginning date? And/or the beginning date is = to or later than
ending date? It seems redundant to mention it twice but but the error
could be made on selecting wrong dates in B3 and/or B4.

2) Same question but setup is:
B3 has the beginning date. B4 an ending date. And/or the beginning date
is = to or later than ending date? It seems redundant to mention it
twice but but the error could be made on selecting wrong dates in B3
and/or B4.

This time, I use Validation dropdown lists in each of B2 to selec the
dates.

How can I display an error message if the ending date is equal to or
prior to beginning date?

 

[Sandy Mann]

Try using Conditional Formatting with:

=AND($B$3<>"",$B$4<>"",$B$3>=$B$4)

The formula in B3 Conditional Formatting and:

=AND($B$4<>"",$B$3<>"",$B$4<=$B$3)

Won't give a error message but will highlight the fac that gthe dates are
wrong.

Or you can have in another cell:

=IF(AND(B3<>"",B4<>"",B3>=B4),"Error in dates!","")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[stef]

Thanks.
Highlighting is not enough as it wouldn't tell the remote user exactly
what is wrong.
The second solution is better. But not quite exactly what I was looking
for--as I was more trying to find a solution similar to a warning
message that a Data Validation option will give, etc., IF that is even
possible.

=IF(AND(B3<>"",B4<>"",B3>=B4),"Error in dates!","")

does work of course.

 

[Sandy Mann]

Depending on how many dates you have it may be possible for you to use Debra
Dalgleish's dependant Validation dropdown:

http://www.contextures.com/xlDataVal13.html

There is a zip file you can download then try changing the names that Debra
has for dates and expanding the range in the Named list. It will mean that
you cannot select an ending date before you have selected a beginning date
and then you will only be presented with dates after the beginning date.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[stef]

That sounds interesting.
I will download it and play around with it; and post the results back here.
Thanks a lot!

 

[Sandy Mann]

I am sure that you will not ahve any trouble because Debra's instruction are
comprehensive, but if you do need any further assisitance then post back
again.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk