displaying error message if end date is prior to begin date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stef - 26 Sep 2007 22:08 GMT

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?

Reply to this Message

Sandy Mann - 26 Sep 2007 22:40 GMT

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!","")

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Excel 2002 SP3
> Win XP HE
[quoted text clipped - 21 lines]
> How can I display an error message if the ending date is equal to or prior
> to beginning date?

Reply to this Message

stef - 26 Sep 2007 22:59 GMT

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.

> Try using Conditional Formatting with:
>
[quoted text clipped - 10 lines]
>
> =IF(AND(B3<>"",B4<>"",B3>=B4),"Error in dates!","")

Reply to this Message

Sandy Mann - 26 Sep 2007 23:42 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Thanks.
> Highlighting is not enough as it wouldn't tell the remote user exactly
[quoted text clipped - 19 lines]
>>
>> =IF(AND(B3<>"",B4<>"",B3>=B4),"Error in dates!","")

Reply to this Message

stef - 27 Sep 2007 00:23 GMT

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

> Depending on how many dates you have it may be possible for you to use Debra
> Dalgleish's dependant Validation dropdown:
[quoted text clipped - 5 lines]
> 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.

Reply to this Message

Sandy Mann - 27 Sep 2007 11:31 GMT

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.

Signature

Regards,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> That sounds interesting.
> I will download it and play around with it; and post the results back
[quoted text clipped - 11 lines]
>> beginning date and then you will only be presented with dates after the
>> beginning date.

Reply to this Message

Rate this thread: