This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Open CSV File With Dates in D/M/Y Format
Problem: Our European subsidiary send me a CSV file. One of the columns is in DD/MM/YYYY format, but my international settings are expecting the dates in MM/DD/YYYY format.
Strategy: You could change your international settings in the Control Panel. However, an easier solution is to rename the .CSV file to a .TXT file and then open the .TXT file in Excel.
As discussed above, you do not see the Text Import Wizard when you open CSV files. When you change the extension, you will get to go through the wizard. In step 3, choose the column and select Date: D/M/Y.
Figure 245 Choose DMY in Step 3.
Additional Details: After the import, the dates will be displayed according to your international settings. In the figure below, 17/01/2014 is displayed as 01/17/2014 per the U.S. date format.
Figure 246 Imported as real dates, they display per your settings.