CODEWITHSUNDEEP
vbaexcelvb6
vulcan raven
vulcan raven

Reputation: 33562

VBA Convert String to Date

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:

Edit:

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

Upvotes: 7

Views: 133105

Answers (3)

littlecodefarmer758
littlecodefarmer758

Reputation: 966

I used this code:

ws.Range("A:A").FormulaR1C1 = "=DATEVALUE(RC[1])"

column A will be mm/dd/yyyy

RC[1] is column B, the TEXT string, eg, 01/30/12, THIS IS NOT DATE TYPE

Upvotes: -1

Mark Hall
Mark Hall

Reputation: 54532

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)

Upvotes: 13

DROP TABLE users
DROP TABLE users

Reputation: 1955

Looks like it could be throwing the error on the empty data row, have you tried to just make sure itemDate isn't empty before you run the CDate() function? I think this might be your problem.

Upvotes: 1

Related Questions