IsDate() Function : VBA

Posted: June 3, 2013 by Theodoulus in Excel, VBA
Tags: , ,

This function returns TRUE if argument’s data type is Date otherwise it returns FALSEHowever, there is an exception. In case the argument type is String the result depends on the contents of the string. If string is in a recognisable date (or time) format then the function returns TRUE.

One could say that the name of the function is misleading since it checks not just for date but time as well.

The Syntax for the function is:            IsDate(expression) 


---------It accepts Date and Time in many formats------------------------ 
IsDate(#01/31/2013#)             'Returns True, as argument is of Date Type
IsDate(#jan-2013#)               'Returns True,as argument is of Date Type
Isdate("January,2013")           'Returns True, as String is in date format 
Isdate(10/15)                    'Return False, It treats it as a double value 
ISDate("10.15")                  'Returns True 
ISDate("10:15")                  'Returns True 
ISDate("10:15 AM")               'Returns True
ISDate("10:15:12")               'Returns True
  1. Like (pseudo)
    =IsDate or TryParseDate
    where TryParseDate uses DateValue function to try parsing string as date.

    Dates are an odd size in Excel and VBA i think. In Excel, date is a numeric value applied with a format. In VBA numeric values are not consideret Dates according to IsDate function, but dates can be compared to numeric values. Take a look:

    Dim d As Date
    d = 41444

    ?IsDate(d) ‘returns true
    ?d = 41444 ‘returns true
    ?IsDate(41444) ‘returns false

    Dim d2 As Date
    d2 = “19-6-2013”

    ?IsDate(d2) ‘returns true
    ?d2 = 41444 ‘returns true
    ?d2 = “19-6-2013” ‘returns true
    ?IsDate(“19-6-2013”) ‘returns true

    I agree IsDate is kinda confusing. I would split it into 2 functions
    IsDate which determines if data type of variable is Date, and
    TryParseDate which determines if string value can be recognized (parsed) as Date

Share your thoughts/feedback

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s