How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)

In this article, we will learn to stop excel from auto formatting dates in CSV. CSV stands for comma-separated values. Sometimes, we need to open or import a CSV file in our excel worksheet. Excel auto formats the date when we open or import any CSV file. Often, it can be undesirable because it changes the data. So, to stop excel from auto formatting dates in CSV, we need to follow some methods. Today, we will demonstrate these methods in easy steps.


Download Practice Book

Download the practice book.


What Is a CSV File?

The file which separates values by a comma is called a CSV file. It is a delimited text file. Each line of a CSV file consists of a data record where each field is separated by a comma. We can import or export a CSV file that contains data records in our excel worksheet very easily.


3 Ways to Stop Excel from Auto Formatting Dates in CSV

To explain the methods, we will use a CSV file that contains information about the ID Number and Date of Birth of some employees.

If we open or import this CSV file in excel, we will see a dataset like below.

And after auto-fitting the columns, we will see the result like below.

Here, if we look at the Date of Birth column, we can see excel has auto formatted the dates. It does not match with our original dataset in the CSV file. For example, if we look at Cell C2, it contains 10-Jul-93. But our CSV file contains 10-July-1993. We want to display the CSV file without auto formatting the dates. The methods below will discuss how to stop excel from auto formatting dates in CSV.


1. Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

We can stop excel from auto formatting dates by saving the CSV file as a Text Document file. It is one of the quickest processes. Let’s follow the steps below to learn this method.

STEPS:

  • In the first place, open the CSV file.
  • Secondly, click on the File tab. A drop-down menu will occur.
  • Select Save As from the drop-down menu. The Save As window will appear.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • Now, change the Save as type to Text Documents(*.txt).
  • After that, click Save to proceed.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • After saving the CSV file as a Text Document file, open Excel.
  • Then, go to the File tab.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • Select Open and then, Browse like the picture below.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • After that, select the Text Document file and click Open. Make sure you have selected All Files in the file type field.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • After clicking Open, you will see a Text Import Wizard on the screen. It is the first step of the Text Import Wizard.
  • Select Delimited in the Original data type field and check the My data has headers option. Then, select Next.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • After clicking Next, the second step of the Text Import Wizard will occur.
  • Check Comma and uncheck Tab in the Delimiters field.
  • Then, click Next.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • After that, the third step of the Text Import Wizard will appear.
  • Now, select the desired column from the Data Preview field. We have selected the Date of Birth column.
  • After selecting the column, select Text in the Column data format field.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

  • Finally, click Finish to see the results below.

Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates

Read More: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)


2. Stop Excel from Auto Formatting Dates by Importing the CSV File

There is another quick way to stop excel from auto formatting dates in CSV files. In this method, we will use the Data tab to import the file. Here, we will use the same CSV file. Let’s pay attention to the steps below to learn more.

STEPS:

  • In the beginning, go to the Data tab and select From Text/CSV. It will open the Import Data window.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • Select the CSV file and click Import. Make sure you have selected All Files in the file type field.

  • After that, a window containing the dataset will occur. Select Transform Data from there.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • After clicking Transform Data, the Power Query Editor will open.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • Next, select the Date of Birth column in the Power Query Editor window.
  • Then, go to the Transform tab and select Data Type. A drop-down menu will occur.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • Select Text from the drop-down menu.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • Instantly, a Change Column Type dialog box will pop up. Select Replace current from there.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • Then, you will find results like below in the Power Query Editor.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • Now, go to the Home tab and select Close & Load.

Stop Excel from Auto Formatting Dates by Importing the CSV File

  • Finally, you will see results like below in the excel worksheet.

Stop Excel from Auto Formatting Dates by Importing the CSV File

Read More: How to Use Formula to Change Date Format in Excel (5 Methods)


Similar Readings:


3. Format the CSV File to Stop Excel from Auto Formatting Dates

In this method, we will format the CSV file to stop excel from auto formatting dates. We will mainly change the data format inside the CSV file. Again, we will use the same CSV file here. Let’s observe the steps below to learn more.

STEPS:

  • Firstly, open the CSV file.
  • Secondly, write:
"John","100","=""10-July-1993"""

Instead of:

"John","100","10-July-1993"

Here, we have added “=” as prefix and “” as a suffix in the Date of Birth column.

  • Follow this format for all data entries.
  • Save it by pressing Ctrl + S and then, close the Notepad after saving it.
  • Next, open Excel and select the File tab.

  • Click Open and then, click Browse.

  • After that, select the CSV file and click Open. Make sure you have selected All Files in the file type field.

  • Finally, you will see results like below.

Related Content: How to Change Default Date Format from US to UK in Excel (3 Ways)


Things to Remember

There are certain things you need to remember to stop excel from auto formatting dates.

  • While opening or importing the CSV file from excel, if you don’t find the file in the desired folder, select All Files instead of All Excel Files.
  • You need to enable the Power Query if you are using Excel 2013 or earlier versions.
  • If you have a large dataset, use Method-1 or Method-2. Method-3 is not appreciated for large datasets.

Conclusion

We have discussed 3 quick and easy methods to stop excel from auto formatting dates in CSV files. I hope these methods will help you to solve your problems related to auto formatting dates in CSV. Furthermore, we have also added the practice book at the beginning of the article. You can download the practice book to exercise. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo