How To Change The Date Format Using Text-To-Column?

[xlmaniac]

Dear All,
I am trying to change the date format using Text-To-Column feature but it is not yielding the desired result.
I was trying the following steps:-
1)ALT+D+E to open the Text-To-Column Wizard.
2)Clicking "Next" button twice to reach the Step 3 of 3.
3)Selecting the Date Format as MDY and then click finish.

Could somebody help me out pls?
Thanks

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 103px"><COL style="WIDTH: 114px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Present Format</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Proposed Format</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">14/01/2012</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">1/14/2012</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Rick Rothstein]

As far as I know, Text-To-Columns does not format cells... I believe all the date option you referred to does is tell Text-To-Columns how to interpret the date parts of text that has the general format of a date.

 

[xlmaniac]

Thanks Rick for sharing the information.
Just a query....What is the use of Date format in Text-To-Column?
Thanks

 

[AlphaFrog]

Try using DMY instead of MDY to convert the dmy-text to serial dates. It worked for me.

Then select column A and format the dates with whatever date format you like.

 

[xlmaniac]

Thank you so much for your input & help......
It also worked for me as well.
Really appreciate your support & co-operation...

 

[Rick Rothstein]

Thanks Rick for sharing the information.
Just a query....What is the use of Date format in Text-To-Column?
Thanks

I misunderstood what you wanted... I locked on the word "format" and missed that you were asking about converting the date to a different interpretation for the layout of the date parts. Sorry for any misdirection I may have caused you.