Excel date formatting for CSV import files

Dates should be formatted in the mm/dd/yyyy format - no periods, dashes, question marks, alpha text.  Other date formats will cause an error during the "Verify" process. Multiple formats in one import should be avoided. 

The following is example of the only acceptable format for January 31, 2012:

All days and months must be represented as two digits (January = 01, February = 02, the fifth day of the month = 05, and so forth).

For those of you who use Excel, be aware that Excel typically strips out the leading zeros in month and day representations.  For instance, if you open a CSV file where the date is formatted as 01/31/2010, it will auto-reformat them to 1/31/2010.  While there is nothing Closerware can do to change the behavior of the application we can offer some advice.

1) You can highlight all date columns, then format the cells' number formatting to be "mm/dd/yyyy" :

To change the date format, all you need to do is open the spreadsheet, highlight the column and right-click and select 'Format Cells'.

Choose 'Date; then highlight 03/14/01, while that is highlighted click on 'Custom'. You will then see: "mm/dd/yy;@" put the cursor on the right hand side of it and delete the ";@" and type "yy" so it says "mm/dd/yyyy" then click ok. This will change the format of all the dates in that column.

NOTE: When you save the file as an Excel file the file will maintain this formatting even if you close the file and re-open it.  However our imports require the file to be in CSV format.  From within Excel you can "Save As" a CSV file.  When the file is saved it will maintain the date formatting as it was at the time of save.  However, if you close and re-open the CSV file from within Excel the program will auto-reformat the date to a m/d/yyyy standard.  This means that if you are making any updates to a CSV file you must again revert the date formatting prior to saving the CSV file. 

The following link from Microsoft can provide more assistance on formatting cells:

Microsoft Help Article (Excel 2003)

Microsoft Help Article (Excel 2007)

2) You can import a CSV file into Excel to avoid the auto-formatting.  We only recommend this for advanced Excel users because the steps can be complicated.  Essentially, you can import a CSV file and specify how each column should be formatted.  If you do this and your dates are formatted properly in the source CSV file (in mm/dd/yyyy or yyyy-mm-dd format) you can set the import format for all date columns to be text (not a number or anything else because it will attempt to transform the values if it can).  The following links from Microsoft can provide more assistance:

Microsoft Help Article (Excel 2003)

Microsoft Help Article (Excel 2007)

Microsoft Online Training for Data Imports (Excel 2003)

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Powered by Zendesk