VBA Convert String to Date

Question

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

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

I saved DeliveryDate values ​​in an array. I need to decide on the basics of the date, and then print the result on a new sheet. So I need to convert the values ​​to an 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, the CDate () function throws an error:

Runtime Error '13':

Type mismatch

Is there a function in VBA that can:

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

Edit:

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

Answer 1

Try using Replace to see if it will work for you. The problem, as I can see, which was mentioned several times above, is that the CDate function suffocates from 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, you don't 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 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) 

Answer 2

It looks like this might cause an error in an empty data row, did you just try to make sure itemDate is not empty before you run the CDate () function? I think this may be your problem.

Answer 3

I used this code:

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

column A will be mm / dd / yyyy

RC [1] - column B, row TEXT, for example, 01/30/12, THIS IS NOT A TYPE DATE