| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing date values
Nick, Thanks for the help. Ken "NickH" wrote: 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 -- Nick "Ken Warthen" wrote: 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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If Then statement comparing date values | Excel Programming | |||
| Help with Comparing values and retrieving values in Excel!!!!!! | Excel Worksheet Functions | |||
| Comparing values between columns only when there are values in bot | Excel Worksheet Functions | |||
| Comparing values in two columns and displaying missing values in n | Excel Programming | |||
| Macro for comparing 2 values (number and date) | Excel Programming | |||
![Ken Warthen[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
