While working with several CSV files, I found myself in a strange situation. I had merged the file contents into a single CSV file. I then opened the file in Excel, only to find that the Date column had data that was a mix of YYYY-MM-DD and MM/DD/YYYY. There are several things that can cause this issue and we’ll look at several fixes.
When I opened the merged CSV file, the date column looked like this:
Those familiar with excel know that if the cell contents aligned to the left are considered text. Cell contents aligned to the right are considered dates and numbers. As we can see, the file above has a mix of dates recognized as dates, and dates recognized as text.
This is the process I follow to format the cells to ISO 8601, or YYYY-MM-DD, my preferred format.
Leading Spaces
If your date, or other numeric column contains a leading space, Excel will treat that cell as text instead of treating the cell as number. The easiest way to verify is to open up the original CSV file and ensure that numeric values do not contain spaces. It’s good practice to ensure data purity before opening with Excel or importing into your database.
Format the Column as Date
The next step formatting the column as Date. There are several ways to format the date column as date. The easiest is selecting the cell or column and clicking on the Number drop down box. From here, pick “Short Date.”
Or, select the cell or column and right click on the selection. Click on Format Cells.
The Format Cell window appears. By default, you will see the Number tab.
Click on “Custom” at the bottom of the list. You’ll see a box labeled with “Type:” which is editable. Type in “YYYY-MM-DD” and click OK. The cell should be formatted correctly.
Use the Excel TEXT function
If formatting does not work, the next step is the Built-in Excel function TEXT. In a new cell, type in the following formula:
=TEXT(A1,"YYYY-MM-DD")Replace A1 with the first date value in your table. The new cell will contain the date formatted properly. You can also populate a new column using the Excel Fill Handle. This will update all of your values in the table.
Text to Columns
You can use text to column at any time to modify data. The text to column tool is found under the Data Menu Item.
Select the target cell and clock on the Text to Columns button. You can work on several cells at a time, just ensure that all of the selected cells have the same issue.
From the step 1 window, select Delimited.
From the step 2 window, select the Comma or Tab. Since we are working with a single cell or column either selection works.
From the step 3 window, select Date and from the combo box, select “MDY”
Click on the Finish button. You will see that the value in the column moved from the left to the right. As we mentioned earlier, values aligned on the right means numbers and dates.
