Date Format from a string

[Portucale]

Hi,
I have a rawdata file which outputs in the date column formatted as Text the month (May-14 ), is there any way that I can format the same May-14 to a date format "mmm-yy"?

Any help and All help is very much appreciated.

Thanks,

 

[Hui]

Have you tried Text to Columns ?

 

[Portucale]

Sorry, should have mention that, yes I did, and yes it works, but this is to an automated report with no intervention from the users, so I was looking for a possible macro or a function.

Thanks,

 

[rumshar]

Hi,
Can't this action(Text to Columns) be automated? I tried doing it with macro recorder and got this code.

Option Explicit

Sub TextToDate()

    With Selection
        .TextToColumns , DataType:=xlDelimited, FieldInfo:=Array(1, 3)
        .NumberFormat = "mmm yy"
    End With
   
End Sub

 

[oldchippy]

Hi,

You could just use this and custom format to mmm-yy

=DATEVALUE(A1)

 

[Deepak]

....so I was looking for a possible macro or a function.

Thanks,

Assume Text data in A1 then try this.

B1=VALUE(A1)