Data Validation issue with two date entries Options

[SS]

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

 

[ManicMiner17]

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

Hi there SS,

The following assumes that Cells A1 and A2 are the only cells you are
validating. The code would need to be revised if this is meant to be a
more general validation.

If you aren't familiar with VBA, this code needs to go in a sheet module
- right click on the sheet tab, select view code, and paste the code in
the pane which appears:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tday As Date
Dim StartDate As Date
Dim Resultdate As Long

Application.EnableEvents = False
Tday = Date

If Range("A1") = "" Or Range("A2") = "" Then
MsgBox "Dates missing!"
Application.EnableEvents = True
Exit Sub
End If

If Range("A1") > Tday Or Range("A2") > Tday Then
MsgBox "No future dates!"
Target = ""
Application.EnableEvents = True
Exit Sub
End If

Resultdate = Range("a2") - Range("A1")

If Resultdate < 0 Then
MsgBox "Start date is later than Target date"
Application.EnableEvents = True
Exit Sub
End If

MsgBox "Date is between Start date and Today"
Application.EnableEvents = True
End Sub

 

[GS]

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

 

[SS]

SS pretended :










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

 

[SS]

SS pretended :










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

 

[GS]

SS presented the following explanation :

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