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

December 20, 2021 - by Bill Jelen

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.

If you have to import your CSV file daily, using Power Query would be easier. Read more starting with "Load and Clean Data with Power Query" on page 410.

In Text to Columns step 3, you can choose a Date and specify the format is MDY or DMY or YMD. In this case, the data coming in uses European date format of 17/01/2014. By choosing DMY as the format, Excel will correctly interpret this as January 17, 2014. — Figure 231. Choose DMY in Step 3.

Problem: I have a column with dates stored as 8 numbers: 20180123 is January 23, 2018.

Strategy: Amazingly, the Text to Columns function can convert these to real dates. In the previous figure, choose YMD as the date format in Step 3.

Problem: When I open a CSV file in Excel, I am losing the leading zeroes. I need those leading zeroes. I would like to choose Text in step 3 of the Import Text to Columns Wizard, but I never get to see that wizard when I open a CSV file.

Strategy: Excel will walk you through the Text to Columns wizard when you open a .txt file. However, if you open a .csv file, Excel will automatically open the file without allowing you to choose field types. If you find that you are losing leading zeroes when you open a .csv file, simply rename the file from .csv to .txt in Windows Explorer before you open the file.

This article is an excerpt from Power Excel With MrExcel

Title photo by Waldemar Brandt on Unsplash