Inconsistant Date Conversion

[MikDNJneer]

Good Afternoon All,

First off, I am new to post to the forum, however I have been utilizing your recommendations for years, and I would like to thank you for teaching a n00b like me how to use VBA to solve tons of business issues.
Here is one that I cannot find support for anywhere.

I am currently using Excel 2007 to import and parse the contents of an email.
I use the "From Text" icon in the "Get External Data" section.
I then change the file type to "All Files" and choose the email that I have previously saved to a directory.
I am able to successfully utilize code to eliminate all the junk rows I do not want, and reformat all the data that I do want with one exception.

There are cells that become popluated with a value that I would like to interpret as a date. Sometimes based on the supplied email, there are no trailing spaces at the end of the string, and excel automatically interprets it as the correct date. However sometimes the cells have a trailing space which I attempt to rectify in the code. I use the trim function to remove the trailing space. Then for some reason excel interpets the date as shown below. I have tested this with several files with the same results.

"30/07/12 " becomes 12/7/2030 = bad
"29/01/13 " becomes 1/29/2013 = good

codes I have tried:
Range("b" & Rownum) = CDate(Format(Trim(Range("A" & Rownum)), "dd/mm/yy"))
Range("c" & Rownum) = Month(Trim(Range("A" & Rownum)))
Range("d" & Rownum) = Day(Trim(Range("A" & Rownum)))
Range("e" & Rownum) = Year(Trim(Range("A" & Rownum)))
Range("f" & Rownum) = Trim(Range("A" & Rownum))
Please help!

 

[b.downey]

Try the following code:

Dim varDate As Variant
    Dim strDate As String
    
    Dim d As Date
    strDate = "30/07/12 "
    varDate = Split(Trim(strDate), "/")
    
    d = CDate(varDate(1) & "/" & varDate(0) & "/" & varDate(2))

You could build it into a function!