VBA Convert String to Date

Question

I have data in an excel sheet in the following format:

ItemCode                            DeliveryDate
5456987                              24.01.2009
5456988                                          
5456989                              12.24.2009
5456990                              12/24/2009

I have stored the values of DeliveryDate in an array. I need to make decision on basics of date and then print the result in a new sheet. So I have to convert the values into array:

Dim current as Date, highest as Date, result() as Date
For Each itemDate in DeliveryDateArray
    current = CDate(itemDate)
    if current > highest then
         highest = current
    end if
    ' some more operations an put dates into result array
Next itemDate
'After activating final sheet...
Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result)

Unfortunately, CDate() function throws the error:

Run-time error '13':

Type mismatch

Is there a function in VBA which can:

parse string with any date format and return a date object to work with.
return an empty date object, if the string is empty or malformed (for comparison in the loop).

Edit:

To reproduce the error, simply run myDate = CDate("24.01.2009")

According to this site [example-code.com/vb/stringtodate.asp] you were doing it right..Where does it throw the error? On which row -- the first one or the second line (empty string)? Have you tried changing the string format? You may want to change the format on how you're parsing your date strings there. "24.01.2009" may not be recognized but "12/24/2009" may -- try to convert the 12/24/2009 only and see if it works. If it does work, then the 24.01.2009 format may be unacceptable. — Ann B. G.
Try converting the 12/24/2009 to see if it works. If it does work, then the format dd.MM.yyyy might be unaaceptable for CDate. There might be a way for you to specify how it's formatted so it can parse it exactly the way you want it. If the dd.MM.yyyy format is what's causing the issue, change the format first like replace the "." with a "/" and try to convert it again. Or format it such that it becomes MM/dd/yyyy and then convert it. I guess I just want to know if "12/24/2009" will error out or not. Check first and let me know. — Ann B. G.
@AnnB., well my first question is; Is there a VBA function which can parse string with any date format and return a date object to work with? — vulcan raven
@JasonWilliams No there is not. It is impossible to know all date formats. — Deanna
@Deanna, I used Replace function to swap '.' with '/' and then used CDate to convert the date. Thanks for the info. :) — vulcan raven

Mark Hall Mark Hall · Accepted Answer · 2012-07-21T04:00:35

Try using Replace to see if it will work for you. The problem as I see it which has been mentioned a few times above is the CDate function is choking on the periods. You can use replace to change them to slashes. To answer your question about a Function in vba that can parse any date format, there is not any you have very limited options.

Dim current as Date, highest as Date, result() as Date 
For Each itemDate in DeliveryDateArray
    Dim tempDate As String
    itemDate = IIf(Trim(itemDate) = "", "0", itemDate) 'Added per OP's request.
    tempDate = Replace(itemDate, ".", "/")
    current = Format(CDate(tempDate),"dd/mm/yyyy")
    if current > highest then 
        highest = current 
    end if 
    ' some more operations an put dates into result array 
Next itemDate 
'After activating final sheet... 
Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result)

VBA Convert String to Date

3 Answers