Text to Date Format

[bachuwar.rajenderprasad]

Dear All,


I have dates in Column "J" like

18.09.2012

21.05.2012

21-05-2012


And I want them to be changed as MM/DD/YYYY format.

09/18/2012

05/21/2012

05/12/2012


PLease help on this.


Regards,

Prasad

 

[sdsurzh]

Hi Prasad,


select all the dates then select text to column option from data menu select delimited==>next==>select Tab==>next==>select date and select DMY from the drop down and finish.


Press Control+1 and custom and paste mm/dd/yyyy. it will work


Thanks,

Suresh Kumar S

 

[keymaster]

If values in Column J are already treated as dates by Excel, then use

=text(J1,"MM/DD/YYYY")


If values in column J are treated as text by excel, then use

=mid(J1,4,2)&"/"&left(J1,2)&"/"&right(J1,4)


Both formulas produce output in text format, which means you cannot use these values for further calculations. Hope that is ok.


For more on this refer to - http://chandoo.org/wp/2010/03/23/text-to-date-convertion/

 

[bachuwar.rajenderprasad]

Hi Suresh,


That helps, but I want this to be used in a Macro. better if there is no manual things.


Prasad

 

[sdsurzh]

Hi Prasad,


Please insert a module and paste the below code it will change the date format.

[pre]

Sub DATEFORMAT()
Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yyyy"
Range("J1").Select
End Sub

[/pre]
Thanks,

Suresh Kumar S

 

[sdsurzh]

Hello Key Master,


Just now i have noticed your comment.


Thanks

Suresh Kumar S

 

[bachuwar.rajenderprasad]

thanks suresh..working fine..

 

[sdsurzh]

Welcome Prasad