How to format dates in CSV file using Excel

fixing date format in CSV vs Excel

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.

Image result for csv to excel
That’s how it converts and shows in a readable format.

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.

change format excel
date format change dropdown
Dropdown to select a format in Microsoft Excel

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.

Follow us to get updates on cryptocurrency taxation
How to format dates in CSV file using Excel
Article Name
How to format dates in CSV file using Excel
Downloading files in CSV (Comma Separated Values), opening them in Microsoft Excel only to find date values messed up is a pain. Here's how to fix it.
Publisher Name
Publisher Logo

Leave a Reply

Your email address will not be published. Required fields are marked *