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  gt  highest then          highest   current     end if       some more operations an put dates into result array Next itemDate  After activating final sheet    Range  quot A1 quot   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  quot 24 01 2009 quot

User · Answer

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

User · Answer

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

User · Answer

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  gt  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] VBA Convert String to Date

Examples related to vba

Examples related to excel

Examples related to vb6