Exporting CSV files from Insightly produces a file with standard U.S. formats, such as dates listed as month/day/year.
If you live in another country, your version of Excel may automatically and incorrectly interpret dates or other fields in a different format, such as day/month/year. This happens when you open one of these files with Excel instead of importing it into Excel.
For example, Excel will incorrectly interpret Insightly's 05/03/2015 as March 5, 2015 and format it as a valid date. And it will not interpret 8/21/2015 as a valid date—because there is no 21st month—and will format the field as text.
This is an Excel hurdle that has bothered international businesses for years, as a quick internet search will demonstrate.
How to open an exported file and convert date formats
- Instead of opening the exported file directly in Excel, save the file to your computer and use Excel's Text Import Wizard, which is usually found under the Data menu or the Get External Data area.The help documentation for your version of Excel provides details on how to use the Text Import Wizard.
- With the wizard, you can identify the date columns that need to be converted and help Excel to interpret them properly. The "General" selection is a good choice for standard text fields. The "Date" selection will display all the different date format options.
This adds a few extra steps to open the file, but they are far fewer steps than trying to clean up the data, row by row.