Summary of this page: Excel will respect your date formatting as long as you save the file as an XLSX (Excel file). Make your changes, format all date columns as shown below, then save the file as an Excel spreadsheet. Use THE EXCEL-TYPE file to make future changes or updates. When you're done and ready to import, Save As and select CSV (comma-separated-values) as the file type. The CSV file will have the proper date format. Just don't open the CSV file in Excel and expect the dates to be formatted correctly. Always use the *.xlsx version to make updates, in order to retain required date formatting.
Some of the CSV imports contain date fields, in particular the capture/schedules import. The required format for date fields is yyyy-mm-dd. This is an international standard date format, designed to reduce confusion.
Many users work with CSV files using a spreadsheet program like Microsoft® Excel. The PROBLEM is that Excel will automatically reformat what it recognizes as a date into the default date format configured for your system. Which may not be yyyy-mm-dd.
What this means is that even if your CSV file is correct (open it in Notepad or Wordpad to see the raw data), the reformatted date field will be wrong in Excel. And after you make changes and save the CSV file, the wrong date format showing in Excel will be applied to the fields.
This is bad, because now your CSV import won't work.
DON'T PANIC! Use the instructions below to change the date format in your spreadsheet. Basic steps: select the date column, right-click and select Format Cells, then select Custom and enter yyyy-mm-dd in the text field.
To reformat the date fields in an Excel spreadsheet
- Open the .csv file in Excel.
-
RIGHT-CLICK the tile above the Start Date column header. (Press control+click if using a Mac)
This selects the entire column and opens a shortcut menu as shown below. - Select Format Cells from the shortcut menu.
- If necessary, select the Number tab (it should appear by default).
- Select Custom from the number Category list.
- In the Type field, replace the text that appears with yyyy-mm-dd.
- Click OK.
- Repeat these steps for the End Date column.
- When finished, Save the file as an Excel file (.xls or .xlsx), then Save As a .csv file.
The saved CSV file will have the proper date formatting for importing to Echo360; the Excel file will retain the format changes and can be used again later to generate a new schedule import file.
If you want to be CERTAIN the CSV file is correct, open the file in Notepad or Wordpad and look for the date; if you changed the format in Excel to yyyy-mm-dd and saved as CSV, the date will appear in this format in the raw csv file. Don't use Excel to check it; Excel will convert the dates back to the system default format.