"How To Excel" Mini-Tutorials by TheExcelAddict.com "Helping Average Spreadsheet Users Become Local Spreadsheet Experts" How To Convert Wrongly Formatted Dates Have you ever copied or imported data into Excel and realized that the date formats were all wrong. For example, your computer may recognize dates in dd/mm/yy format and you've just copied data, that includes a date column, from another source and the date format there is mm/dd/yy. You'll notice that Excel recognizes some of the dates but for most it won't. You'll see the dates that Excel has recognized as right-aligned cells and those it didn't recognize, and assumed was text, as left-aligned cells. If you look closely you'll see that even the cells Excel did recognize as dates are wrong - the months and days are switched. It can be quite a task to fix all of these dates. In the early days I tried various techniques to solve this problem, but all of them were time consuming. Then I discovered a simple little trick using Excel's 'Text to columns' option. Here's what you can do... Highlight the cells (only one column wide) containing the dates. You can select the entire column if you like. From the Data menu, select 'Text to columns' In the dialog box, select the 'Fixed width' option and click the Next button If there are any column break lines (vertical lines with arrows) through the data area, double-click on them to remove them all. Then click the Next button In the 'Column data format' section, select MDY (or whichever date format you need) from the Date dropdown. This tells Excel the format of the imported dates. Click the Finish button. Just like magic, Excel reads all of the imported MDY dates and converts them to DMY format. All of those dates have been fixed in just a couple of seconds. Now, isn't that cool? Why not print (CTRL+P) this tip and share it with your friends and associates? Want more great Excel tips just like this one? Subscribe to my FREE "Spreadsheet Tips From An Excel Addict" newsletter at the www.TheExcelAddict.com. Copyright © by Francis Hayes (The Excel Addict) All Rights Reserved