Hello,
Cell A1 is a 'start' date, and cell A2 is a 'completed' date.

I had it working so that cell A2 must be a date between cell A1's
date
and Today(). I used:


between


=A1


and


=Today()


*** This worked great, except if cell A1 was empty(blank) before A2
date is entered; it will allow the mismatch (a completed date prior
to
start date).


*** I need a way to make the data validation error to appear when
cell
A1 is empty (not been entered yet) and also the error to appear when
the original criteria is not met (date between cell A1's date and
Today() ).


This seemed really easy to fix at first, but i can't get anything
working.
Any help would be much appreciated.


Many Thanks,
SS

SS pretended :
> Hello,
> Cell A1 is a 'start' date, and cell A2 is a 'completed' date.
>
> I had it working so that cell A2 must be a date between cell A1's
> date
> and Today(). I used:
>
>
> between
>
>
> =A1
>
>
> and
>
>
> =Today()
>
>
> *** This worked great, except if cell A1 was empty(blank) before A2
> date is entered; it will allow the mismatch (a completed date prior
> to
> start date).
>
>
> *** I need a way to make the data validation error to appear when
> cell
> A1 is empty (not been entered yet) and also the error to appear when
> the original criteria is not met (date between cell A1's date and
> Today() ).
>
>
> This seemed really easy to fix at first, but i can't get anything
> working.
> Any help would be much appreciated.
>
>
> Many Thanks,
> SS

suggestion...

Use Conditional Formatting to 'flag' either cell if its counterpart is
empty while it contains a date. Use Data Validation to ensure only a
valid date can be entered.

So CF for A1 would be:
=AND(A1="",A2<>""); Format cell with red fill

The CF for A2 would be:
=AND(A2="",A1<>""); Format cell with red fill

Now if both cells are empty then all is normal. If you enter a start
date in A1 then A2 will flag red to indicate a finish date is required
at some point. If you enter a finish date in A2 without a start date in
A1 then A1 will flag red to indicate a start date is required.

Use DV only to restrict the input to dates.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Sep 11, 5:19*pm, GS <gesan...@netscape.net> wrote:
> SS pretended :
>
>
>
>
>
> > Hello,
> > Cell A1 is a 'start' date, and cell A2 is a 'completed' date.
>
> > I had it working so that cell A2 must be a date between cell A1's
> > date
> > and Today(). *I used:
>
> > between
>
> > =A1
>
> > and
>
> > =Today()
>
> > *** This worked great, except if cell A1 was empty(blank) before A2
> > date is entered; it will allow the mismatch (a completed date prior
> > to
> > start date).
>
> > *** I need a way to make the data validation error to appear when
> > cell
> > A1 is empty (not been entered yet) and also the error to appear when
> > the original criteria is not met (date between cell A1's date and
> > Today() ).
>
> > This seemed really easy to fix at first, but i can't get anything
> > working.
> > Any help would be much appreciated.
>
> > Many Thanks,
> > SS
>
> suggestion...
>
> Use Conditional Formatting to 'flag' either cell if its counterpart is
> empty while it contains a date. Use Data Validation to ensure only a
> valid date can be entered.
>
> So CF for A1 would be:
> * =AND(A1="",A2<>""); Format cell with red fill
>
> The CF for A2 would be:
> * =AND(A2="",A1<>""); Format cell with red fill
>
> Now if both cells are empty then all is normal. If you enter a start
> date in A1 then A2 will flag red to indicate a finish date is required
> at some point. If you enter a finish date in A2 without a start date in
> A1 then A1 will flag red to indicate a start date is required.
>
> Use DV only to restrict the input to dates.
>
> HTH
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -
>
> - Show quoted text -

ManicMiner17 and GS,
Thanks for the replies. Unfortunately, the excersize here is to 'not'
use any VB code (I wish i could). Using macros increases the
validation effort 10 fold at our company and hense the reason I am
stuck using the standard excel toolbox.

I was hoping there was a way to use either Data Validation with Dates,
or the Custom option (which i haven't had luck with either).

It seems that any of my solutions would work if only the validation
formulas used would actually detect a cell with no entry, or a blank
cell, and throw a False answer, initiating the data validation error
set up.

I just want to prevent an entry in cell A2 (with the DV error message)
when cell A1 was empty (yet to be filled in). Is that soo much to
ask :-)

Any further suggestions, using non-vb, will again be much appreciated.

Thanks,
SS

On Sep 11, 5:19*pm, GS <gesan...@netscape.net> wrote:
> SS pretended :
>
>
>
>
>
> > Hello,
> > Cell A1 is a 'start' date, and cell A2 is a 'completed' date.
>
> > I had it working so that cell A2 must be a date between cell A1's
> > date
> > and Today(). *I used:
>
> > between
>
> > =A1
>
> > and
>
> > =Today()
>
> > *** This worked great, except if cell A1 was empty(blank) before A2
> > date is entered; it will allow the mismatch (a completed date prior
> > to
> > start date).
>
> > *** I need a way to make the data validation error to appear when
> > cell
> > A1 is empty (not been entered yet) and also the error to appear when
> > the original criteria is not met (date between cell A1's date and
> > Today() ).
>
> > This seemed really easy to fix at first, but i can't get anything
> > working.
> > Any help would be much appreciated.
>
> > Many Thanks,
> > SS
>
> suggestion...
>
> Use Conditional Formatting to 'flag' either cell if its counterpart is
> empty while it contains a date. Use Data Validation to ensure only a
> valid date can be entered.
>
> So CF for A1 would be:
> * =AND(A1="",A2<>""); Format cell with red fill
>
> The CF for A2 would be:
> * =AND(A2="",A1<>""); Format cell with red fill
>
> Now if both cells are empty then all is normal. If you enter a start
> date in A1 then A2 will flag red to indicate a finish date is required
> at some point. If you enter a finish date in A2 without a start date in
> A1 then A1 will flag red to indicate a start date is required.
>
> Use DV only to restrict the input to dates.
>
> HTH
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -
>
> - Show quoted text -

Oh, and to Gary, I don't really want to use conditional formatting in
the cell, as this is much too easy to defeat, meaning one can copy the
cell to any other unlocked cell, and the
formatting goes with it; causing usually benign, but undesirable
results.

Thanks Again,
SS

SS presented the following explanation :
> On Sep 11, 5:19*pm, GS <gesan...@netscape.net> wrote:
>> SS pretended :
>>
>>
>>
>>
>>
>>> Hello,
>>> Cell A1 is a 'start' date, and cell A2 is a 'completed' date.
>>> I had it working so that cell A2 must be a date between cell A1's
>>> date
>>> and Today(). *I used:
>>
>>> between
>>
>>> =A1
>>
>>> and
>>
>>> =Today()
>>
>>> *** This worked great, except if cell A1 was empty(blank) before A2
>>> date is entered; it will allow the mismatch (a completed date prior
>>> to
>>> start date).
>>
>>> *** I need a way to make the data validation error to appear when
>>> cell
>>> A1 is empty (not been entered yet) and also the error to appear when
>>> the original criteria is not met (date between cell A1's date and
>>> Today() ).
>>
>>> This seemed really easy to fix at first, but i can't get anything
>>> working.
>>> Any help would be much appreciated.
>>
>>> Many Thanks,
>>> SS
>>
>> suggestion...
>>
>> Use Conditional Formatting to 'flag' either cell if its counterpart is
>> empty while it contains a date. Use Data Validation to ensure only a
>> valid date can be entered.
>>
>> So CF for A1 would be:
>> * =AND(A1="",A2<>""); Format cell with red fill
>>
>> The CF for A2 would be:
>> * =AND(A2="",A1<>""); Format cell with red fill
>>
>> Now if both cells are empty then all is normal. If you enter a start
>> date in A1 then A2 will flag red to indicate a finish date is required
>> at some point. If you enter a finish date in A2 without a start date in
>> A1 then A1 will flag red to indicate a start date is required.
>>
>> Use DV only to restrict the input to dates.
>>
>> HTH
>>
>> --
>> Garry
>>
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -
>>
>> - Show quoted text -
>
> Oh, and to Gary, I don't really want to use conditional formatting in
> the cell, as this is much too easy to defeat, meaning one can copy the
> cell to any other unlocked cell, and the
> formatting goes with it; causing usually benign, but undesirable
> results.

I don't see how you're going to accomplish a non VBA solution using
Excels built-in features (such as the suggestions I made) if you refuse
to use them. I bid you good luck!

Since the cells you're trying to manage are preformatted as Date data,
what does it matter if someone copies that format to other unlocked
cells. Obviously, your project needs some user instructions so people
don't do stupid things!<IMO>
>
> Thanks Again,
> SS

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc