Date Validation Error

[WildBurrow]

I have the following code to ensure valid date;

'Discovery date not valid
    If Not Application.Intersect(target, range("I3")) Is Nothing Then
        If (Not IsDate(target.Value)) Then
            Application.EnableEvents = False
            target.Value = Empty
            target.Activate
            Application.EnableEvents = True
            MsgBox "Please enter valid date and time.", vbOKOnly + vbExclamation, "Discovery Date and Time Format:   mm/dd/yy hh:mm"
        End If
    End If

The cell has a custom format for date as mm/dd/yy hh:mm

I've just discovered that is someone adds a zero to the beginning of the month, it isn't triggering the message. For example:
Actual date/time: 04/02/24 17:49
Entered date/time 004/02/24 17:49

Any idea why the IsDate function isn't recognizing this as an error?

 

[6StringJazzer]

When someone actually types "004/02/24 17:49" into a cell, it is not recognized as a date/time, and it is stored as text. Therefore IsDate will return FALSE.

BTW that will also screw up any calculations on the worksheet using that date.

 

[Akuini]

Try adding string pattern "##/##/## ##:##" as a criteria:

    If Not Application.Intersect(Target, Range("I3")) Is Nothing Then
        If (IsDate(Target.Value)) And Target.Text Like "##/##/## ##:##" Then 'string pattern corresponding to "mm/dd/yy hh:mm"
            'do nothing
        Else
            Application.EnableEvents = False
            Target.Value = Empty
            Target.Activate
            Application.EnableEvents = True
            MsgBox "Please enter valid date and time.", vbOKOnly + vbExclamation, "Discovery Date and Time Format:   mm/dd/yy hh:mm"
        End If
    End If

 

[kvsrinivasamurthy]

Try.
Instead of

  If (Not IsDate(target.Value))  Then

Use

  If (Not IsDate(target.Value)) And Not WorksheetFunction.IsText(target.Value) Then

 

[WildBurrow]

When someone actually types "004/02/24 17:49" into a cell, it is not recognized as a date/time, and it is stored as text. Therefore IsDate will return FALSE.

BTW that will also screw up any calculations on the worksheet using that date.

6StringJazzer:
"BTW that will also screw up any calculations on the worksheet using that date." .....Yep, it did just that.

 

[WildBurrow]

Try adding string pattern "##/##/## ##:##" as a criteria:

    If Not Application.Intersect(Target, Range("I3")) Is Nothing Then
        If (IsDate(Target.Value)) And Target.Text Like "##/##/## ##:##" Then 'string pattern corresponding to "mm/dd/yy hh:mm"
            'do nothing
        Else
            Application.EnableEvents = False
            Target.Value = Empty
            Target.Activate
            Application.EnableEvents = True
            MsgBox "Please enter valid date and time.", vbOKOnly + vbExclamation, "Discovery Date and Time Format:   mm/dd/yy hh:mm"
        End If
    End If

Akuini:

This did the trick. Thank you for your quick reply.

 

[WildBurrow]

Try.
Instead of

  If (Not IsDate(target.Value))  Then

Use

  If (Not IsDate(target.Value)) And Not WorksheetFunction.IsText(target.Value) Then

kvsrinivasamurthy,
I tried your code but found that it did nothing. It failed to trigger the message box. This is how I've typed it:

'Discovery date not valid
    If Not Application.Intersect(target, range("I3")) Is Nothing Then
        If (Not IsDate(target.Value)) And Not WorksheetFunction.IsText(target.Value) Then
            Application.EnableEvents = False
            target.Value = Empty
            target.Activate
            Application.EnableEvents = True
            MsgBox "Please enter valid date and time.", vbOKOnly + vbExclamation, "Discovery Date and Time Format:   mm/dd/yy hh:mm"
        End If
    End If

 

[kvsrinivasamurthy]

TRy

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, Range("I3")) Is Nothing Then
   Dim K As Double
   On Error Resume Next
   K = Target.Value
        If K = 0 Then
            Application.EnableEvents = False
            Target.Value = Empty
            Target.Activate
            Application.EnableEvents = True
            MsgBox "Please enter valid date and time.", vbOKOnly + vbExclamation, "Discovery Date and Time Format:   mm/dd/yy hh:mm"
        End If
    End If

End Sub

 

[WildBurrow]

TRy

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, Range("I3")) Is Nothing Then
   Dim K As Double
   On Error Resume Next
   K = Target.Value
        If K = 0 Then
            Application.EnableEvents = False
            Target.Value = Empty
            Target.Activate
            Application.EnableEvents = True
            MsgBox "Please enter valid date and time.", vbOKOnly + vbExclamation, "Discovery Date and Time Format:   mm/dd/yy hh:mm"
        End If
    End If

End Sub

That worked. Thank you.

There is a slight performance difference between your code and the code offered by Akuini (suggested identifying the string pattern).
If a user put in "AM" or "PM" in the cell, Akuini's code corrected the entry to the 24-hour time formatting.
Your code picked up on the "AM" or "PM" as text and triggered the error message.

However, both did the trick.