Excel/Text to column for DATE DMY/MDY

I want to change date format using "Text to column" but it is not working. I do not understand what is logic and how it work.
I really appreciate if you help me to understand this.

Screen-shot attached for detail.

Regards,
Ram

AnswerHumans are very good at understanding dates, because we always follow simple rules - but different people have different rules, such that 04/05 could be 4th may or April 5th - and it is this that is causing the problem. To get round it, I would do a two stage process, rather than rely on Excel interpretting the dates correctly. In a blank column (for simplicity the following assume that your data starts in cell A2 and extends downwards) use the following formula

=DATE("20"&RIGHT(A2,2),MID(A2,4,2),LEFT(A2,2))

(I've assumed that all dates will be current century, hence the addition of the "20" to ensure the two digit date format gets correctly interpreted). Extend this formula down to cover the entire date range, then copy and use paste special, values in the destination area. You should then have the data you need in the format you need, and the "helper" column with the formulas in can be removed.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for
all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/CredentialsI'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!