Open csv in excel - Date format issue

You may jump to :

• Why excel do not recognize all the dates in CSV:
• How do I understand if excel could open my csv?
• Understand Date Locale:
• Solution 1: Change the Locale or Region and Language
• Solution 2 : Use Text Import Wizard
• Solution 3: Use latest version of Office

Very often people face issues when they are opening csv file and that have dates. When date is exported or saved most of the program or applications uses Month-Day-Year. If your locale or Region & Language (Date format : position of day and month) is not the same, then excel do not understand all the dates and create inconsistencies. We will see how to overcome that situation in different ways. But before that let us see why we are facing this issue. This can help you to think some other way to solve your situation

Excel internally stores date information as a number and while displaying on screen, it shows the date in a format that is setup on your computer as Region and Language settings. If you put 0 in a cell and change the format of the cell as date, you will see it as 1st January, 1990. On the other hand, in CSV or a TEXT file all data are texts only. Excel needs to convert it to date, otherwise, calculations like adding dates, or comparing it with other dates, filtering for year etc is not possible. To interpret or understand the data in CSV, excel uses Region and Language setting. While using that, excel determines which part of date text from csv is the day and which part is the month. Suppose, it has determined that the first part is the month. Now, during converting that to date, if the month section is more than 12, it cannot consider the entire data as date. So, whenever it is not able to convert that to date, it will keep that as Text and rest of that will be converted to DATE data. If there is a single case of Excel not able to convert that as date data, I would assume that the csv have date from different Region and Language and all the date conversion is incorrect. You can see below the Region and Language settings. depending on your windows version the look may be different

Usually, Texts are left aligned in excel. Numbers are right aligned. If you increase the column width, you can clearly see some dates are left aligned and some of them are right aligned. Left aligned are the ones that have not been considered as Date data. But Obviously, in this case the date that has been considered date are wrong. As you can see, if the dates format in the source CSV was same as the Region and Language (Locale) settings, then all of the date should have been in the date format. This is pictorially explained in the picture below

How Do I solve the puzzle?
There are different ways you can address this issue. I would cover three methods in this article

Get the information or determine yourself if the data has UK Date format (DD/MM/YY) or US Date format (MM/DD/YY). If you are unsure about it, look at the CSV file for a date related field and determine which section represents Days. It should have number greater than 12. You would be able to eliminate the year field quickly looking at the data as same Year value would be there for large number of data. If the date is the First part then it is UK format otherwise it should be US. There is one more well known date format and that is ISO. It starts with Year followed by Month and subsequently by Date

Easiest solution is to change the Locale or Region and Language setting of your computer to that of the Source Data.

On your windows Search type in Region and Language. You will see it under control Panel. Open it and change the date format to match the source file. open the csv file and verify that the data has been correctly interpreted by Excel. You may save the data as excel (use Save as). After saving the data, if need be, you can change the Region and Language setting back to the original.

Open the file as plain text file and define date Columns.

1. Rename the file to change or add extension as txt. For example test.csv file is changed to test.csv.txt
2. Open Excel .
3. Use Menu File -> Open and then browse to the file.
4. This will invoke Excel's Text Import Wizard. Follow the next instructions and illustrations
5. First Select Delimited and click Next

6. Select the delimiter as per your file. in this example the delimiter used is comma(,) . Now Excel understand the data fields. you can see data are seperated from each other by a vertical line. Please click Next

7. Now, at step 3 of the Text Import wizard, you have to make Excel understand the type of data that each column or field contains. First Select the column, where you have the date data. Next, select the Date option. Then from the dropdown select the date field type option. If your source data starts with Month followed by Day and then Year, then select MDY .. and so on

There is a limitation - if a single row contains date and Time both, excel fails to interpret that correctly. So, we need to force excel to consider that data as test and not to do any operation on it.

8. Select the field that has both Data and Time together. Then select Data type as Test and click on finish

You see the final result here

Latest version of excel have overcome these issues. You have to use Get Data options under Data to import the data. This wizard understands different date data format including data which is a combination of Date and Time. This do not give you an option to choose different Date formats but it understands it correctly. I have checked different complex options and it worked in all the combinations. There is one catch. The imported data are stored in a table in Excel. You can use Convert to Range from Table contextual menu

Hope that this solves your CSV import issue in excel