none
Date validation RRS feed

  • Question

  • Using Office 365(Excel 2016) and Windows 10.

    I wish to confirm that the entry in a cell is a valid date.  That is, that it is a real date, not just in an acceptable date format.  If I use the following code:

    Sub TestValidDate()
    Dim booDate As Boolean

        booDate = True
        If Not IsDate(Selection) Then
            booDate = False
            MsgBox "Not Date."
        End If
       
    End Sub

    When the selected cell contains 112/7/16, the code (wrongly?) recognises it as a date and does not produce the message box or change the booDate value.  (NB I am using UK format dd/mm/yy, and the cell is formatted as Date).  However, if I type into the immediate window ?isdate(112/7/16), it returns "False".  Investigating further, I typed into the immediate window ?isdate(12/7/16), which is a perfectly valid date, and it still returned "False", which was confusing.  When my selected cell contains text (e.g."asd") the procedure works as expected and I get the Not Date message box.  Can anyone please give guidance into how to identify non-date entries, such as a three digit day or month?

    With thanks

    Andy C

    Thursday, July 28, 2016 2:23 PM

Answers

  • If you want to return False for a text value that looks like a valid date, you could use this function:

    Public Function IsADate(cel As Range) As Boolean
        On Error GoTo ExitHere
        IsADate = IsDate(cel.Value)
    ExitHere:
    End Function

    If you want to return True for a text value that looks like a valid date, you could involve the number format of the cell:

    Public Function IsADate(cel As Range) As Boolean
        On Error GoTo ExitHere
        IsADate = (Format(cel.Value, cel.NumberFormatLocal) = cel.Text)
    ExitHere:
    End Function

    Use like this in a cell formula:

    =IsADate(A1)

    and like this in other VBA code:

        If IsADate(Worksheets("Sheet1").Range("A1")) Then
            ...
        Else
            ...
        End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AndyColRomsey Thursday, July 28, 2016 6:17 PM
    Thursday, July 28, 2016 5:08 PM

All replies

  • You need to use # around the date: IsDate(#112/7/16#) which on my system with yyyy-mm-dd is translated to IsDate(#7/16/112#)

    Maybe that put you on the right track for further troubleshooting.


    Best regards, George


    Thursday, July 28, 2016 2:50 PM
  • George

    Thanks for looking at that.  In the immediate window ?isdate(#12/7/16#) returns "True", which is encouraging, but unfortunately ?isdate(#112/7/16#) also returns true, which is less encouraging!

    Andy C

    Thursday, July 28, 2016 3:27 PM
  • That is because the immediate window is just that. Type isdate(#112/7/16#) in the VBA code, run it and it will autocorrect to what VBA think it is the date.

    Best regards, George

    Thursday, July 28, 2016 3:34 PM
  • George

    Thanks again. When I put the #s into the code with the line now reading

     If Not IsDate(#12/7/16#) Then  

    it works fine.  But if the line reads

    If Not IsDate(#112/7/16#) Then  

    when I hit return to go to the next line I get the error message "Compile Error:  Expected expression" with the first # highlighted.  So something recognises that it is not a date!

    Andy C

    Thursday, July 28, 2016 3:55 PM
  • Excel 2016:

    I start with

    Sub test()

    IsDate(#112/7/16#)

    End Sub

    and when I hit return the expression changes to:

    IsDate (#7/16/112#)


    Best regards, George

    Thursday, July 28, 2016 4:14 PM
  • Thanks again George.

    That works for me too, but I can't see how it can be applied to my original problem of how I find whether the contents of a selected cell is in a valid date format.  Using my original code:

    Sub TestValidDate()
    Dim booDate As Boolean

        booDate = True
        If Not IsDate(Selection) Then
            booDate = False
            MsgBox "Not Date."
        End If
      
    End Sub

    I had hoped that when it ran with the selected cell, formatted as UK date, containing 112/7/16, it would show the message box "Not Date." and would set the variable booDate to false.

    Andy C

    Thursday, July 28, 2016 4:29 PM
  • This seems like to be a "semi-bug". Set cell A1 to 112/7/16 and the run the below code.
    It will fail on date but succeed on string. Go figure.

    Option Explicit

    Sub test()
    Call test1
    Call test2
    End Sub

    Sub test1()
    Dim r As Range
    Dim d As Date

    Set r = Range("A1")
    d = r.Value
    Debug.Print IsDate(d)
    End Sub

    Sub test2()
    Dim r As Range
    Dim s As String

    Set r = Range("A1")
    s = "#" & r.Value & "#"
    Debug.Print IsDate(s)
    End Sub


    Best regards, George


    Thursday, July 28, 2016 4:46 PM
  • George

    Thanks for that.  The results are as you describe.  However, when A1 is a genuine date (12/7/16) it still produces the same result.  But it then converts the date to 12/07/2016 in A1 (probably because of the date format I have selected).

    This is going to require some thinking about, but I'm grateful for your input.  If I get an answer I'll post it here.

    Andy C

    Thursday, July 28, 2016 5:02 PM
  • If you want to return False for a text value that looks like a valid date, you could use this function:

    Public Function IsADate(cel As Range) As Boolean
        On Error GoTo ExitHere
        IsADate = IsDate(cel.Value)
    ExitHere:
    End Function

    If you want to return True for a text value that looks like a valid date, you could involve the number format of the cell:

    Public Function IsADate(cel As Range) As Boolean
        On Error GoTo ExitHere
        IsADate = (Format(cel.Value, cel.NumberFormatLocal) = cel.Text)
    ExitHere:
    End Function

    Use like this in a cell formula:

    =IsADate(A1)

    and like this in other VBA code:

        If IsADate(Worksheets("Sheet1").Range("A1")) Then
            ...
        Else
            ...
        End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AndyColRomsey Thursday, July 28, 2016 6:17 PM
    Thursday, July 28, 2016 5:08 PM
  • Thursday, July 28, 2016 5:09 PM
  • Hans

    As always, a clear answer.  Many thanks.

    Andy C

    Thursday, July 28, 2016 6:19 PM