It is a pain that Microsoft makes us feel when we open CSV files in Excel. Since we don’t look at excel sheets unless it is tax time and we don’t have a choice at the moment (if using windows), we need to understand how to format dates in CSV file using Excel.

CSV file means it is not a Microsoft Excel format file. It is a simple file containing Comma Separated Values. Excel is actually doing us a favor by aligning data into separate cells and making it readable. So, can’t curse it either.

However, when Excel tries to help us and convert, it misinterprets certain column values. Most commonly misinterpreted column would be a date-time column. Since Date has various different formats to display, Excel software could interpret it in various ways and display it. One of which might not be understandable by us or something we were not expecting.

To deal with this, when a date column becomes unreadable when opened in excel, it is important for you to notice that and fix it by formatting the column appropriately. Ignoring this could save the file with wrong values and when sent to another party or tried to upload on software like BearTax could result in errors or inaccurate results.

To fix this, select the column and change the type from General to Date as shown below.

Select “Short Date” or “Long Date” whichever fits your needs and then you can save the file. If you need to format the date in other formats, you can select “More Number Formats” and provide the desired format there (Eg: dd/MON/YYYY etc.,)

Hope this helps you with your formatting issues and get taxes or other excel job done quickly.