ISDATE function in VBA evaluating to false and i dont know why

**welchs101** · 02-07-2013, 09:02 AM

Hi, i have a macro where i use the vba function ISDATE.

I pass the isdate function two date formats.........but for some reason both evaluate to "false" and i am not sure why.

i am enclosing small macro to show the problem i am having........

Any ideas?

**mrice** · 02-07-2013, 10:43 AM

Try declaring the variables as dates

Sub TEST()

Dim fl_macro As String
Dim userdate As Date
Dim userdate2 As Date

fl_macro = ThisWorkbook.Name

Set ws = Workbooks(fl_macro).Worksheets(1)


userdate = ws.Range("A1").Value2        'note...A1 is a date 1/12/12

userdate2 = 40920



If IsDate(userdate) Then
    MsgBox ("user date is a date")
    
ElseIf IsDate(userdate2) Then
    MsgBox ("userdate2 is a date")
    
ElseIf Not IsDate(userdate) Then
    MsgBox ("userdate is NOT a date ")
    
ElseIf Not IsDate(userdate2) Then
    MsgBox ("userdate2 is NOT a date")

End If

End Sub

**RHCPgergo** · 02-07-2013, 10:44 AM

Hi,

you are using the Value2 property and it returns a Double data type if the original data in the range is Date. Try using the Value property.
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

Note: you didn't declare some of your variables, not sure if that's intentional.

**AB33** · 02-07-2013, 10:46 AM

If you change this line

userdate = ws.Range("A1").Value2

INTO

userdate = ws.Range("A1").Value

**welchs101** · 02-07-2013, 12:05 PM

thanks....i will take a look at your suggestions

during the time i posted this question........the server went dead for this web site...........during that time i was thinking about what to do.......

i came up with this idea of a validDate function.........it seems to work......i am going to include it below.......but it figures that i would go to a "long" soln when (looking at the suggestions thus far) when the real soln might be very simple.

Function ValidDate(date_test As Variant)

    On Error GoTo yes_error
    
    
    If date_test = Null Or Len(date_test) = 0 Then
        ValidDate = False
        Exit Function
    End If
    
    
    If IsDate(CDate(date_test)) Then
        ValidDate = True
        GoTo no_error
    Else
        ValidDate = False
        GoTo no_error
    End If
    
    
    
no_error:
    On Error GoTo 0
    Exit Function
    
    
yes_error:
    ValidDate = False
    On Error GoTo 0
    Exit Function


End Function

**welchs101** · 02-07-2013, 04:32 PM

this is not the first time i have run across the ".value" vs ".value2" issue.

Any suggestions on when to use one vs the other? I read something somewhere that i should always use the ".value2" which is why i use it all the time.......but from time to time when working with dates i have an issue but not all the time.

any suggestions/advice?

**JosephP** · 02-07-2013, 06:47 PM

if you need dates to be treated as dates (and currency to be treated as such) use value otherwise use value2. the latter is often faster and can be safer if you may run into conversion issues such as date formatting-which is only an issue with non US formats and text representations of dates