How to convert a TEXT to a DATE?

[ko1]

I need to bank account reconciliation. Unfortunately the file I exported out of my bank account shows "'02/05" (note the single apostrophe mark).

I tried doing a RIGHT function to get "02/05" and then I used DATEVALUE to convert it to a DATE - which however automatically shows as Feb 5 - when in fact, it should be May 2 (as I am using Asian calendar).

What is the easiest way to convert this "'02/05" to "May 2"?

Thanks!

 

[Deepak]

You didn't got the may2 because you have different date format on pc.

What i used.
=DATE(2016,RIGHT(A2,2),MID(A2,2,2))
=DATEVALUE(RIGHT(A2,5))

 

[ko1]

@Deepak, Many thanks, sir! Works now!

 

[Deepak]

You may also use TextToColumns like as below.

    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Space:=False, Other:=True, OtherChar:="'", FieldInfo:=Array(Array(1, 9), Array(2, 4))

Backup ur data before doing so..

 

[Naresh Yadav]

you can also try =SUBSTITUTE(A2,"'","")+0 and than format it by going in to format cell-Custom- and than type mmm-dd in "TYPE heading