Comparing date values

[Ken Warthen]

I'm using the following code to compare to date values. The values are from
two cells (start date, end date) on a spreadsheet. I need to insure the end
date is 90 days greater than the start date. The cells are formatted as
dates. A subroutine (sValidateDate) is called from code triggered in the
Worksheet_Change event.

When I step through the code with an end date less than 90 days greater than
the start date, the code within the IF strEndDate > strStartDate statement
does not trigger. Any idea on what I'm doing wrong here?

Ken Warthen
(e-mail address removed)

Private Sub sValidateDate(Target As Range, Optional EndDate As Boolean)
Dim strStartDate
Dim strEndDate


If Target <> "" Then
Select Case EndDate
Case False 'Target is a start date
If CVDate(Target.Value) < Date Then
MsgBox "Date must be greater than current date.",
vbOKOnly + vbExclamation, "Invalid Date Entry"
Cells(Target.Row, Target.Column) = ""
Cells(Target.Row, Target.Column).Activate
End If
Case True 'Target is an end date
strStartDate = Cells(Target.Row - 1, Target.Column).Value
strEndDate = Target.Value
strStartDate = strStartDate + 90
If strEndDate > strStartDate Then
MsgBox "Date must be at least 90 days greater than start
date.", vbOKOnly + vbExclamation, "Invalid Date Entry"
Cells(Target.Row, Target.Column) = ""
Cells(Target.Row, Target.Column).Activate
End If
Case Else
End Select
End If

End Sub

 

[NickH]

Hi Ken


It looks like your test is the wrong way around. Try If strEndDate <
strStartDate then ..

A simpler way to achieve the same result is:

Private Sub sValidateDate(Target As Range, Optional EndDate As Boolean)
If Not IsDate(Target) Then Exit Sub
If EndDate Then
If (Target.Offset(-1, 0) - Target) >= 90 Then Exit Sub
MsgBox "Date must be at least 90 days greater than start date.",
vbOKOnly + vbExclamation, "Invalid Date Entry"
Else
If Target >= Date Then Exit Sub
MsgBox "Date must be greater than current date.", vbOKOnly +
vbExclamation, "Invalid Date Entry"
End If
Target = ""
Target.Activate
End Sub

 

[Ken Warthen]

Nick,

Thanks for the help.

Ken

Hi Ken


It looks like your test is the wrong way around. Try If strEndDate <
strStartDate then ..

A simpler way to achieve the same result is:

Private Sub sValidateDate(Target As Range, Optional EndDate As Boolean)
If Not IsDate(Target) Then Exit Sub
If EndDate Then
If (Target.Offset(-1, 0) - Target) >= 90 Then Exit Sub
MsgBox "Date must be at least 90 days greater than start date.",
vbOKOnly + vbExclamation, "Invalid Date Entry"
Else
If Target >= Date Then Exit Sub
MsgBox "Date must be greater than current date.", vbOKOnly +
vbExclamation, "Invalid Date Entry"
End If
Target = ""
Target.Activate
End Sub