Excel: Open CSV File With Dates in D/M/Y Format

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

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.

  1. Choose DMY in Step 3.