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.
Humans 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
(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.
(question found in question pool)
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