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, click Save As and select CSV (comma-separated values) as the file type. The CSV file will have the proper date format. Do not open the CSV file in Excel and expect the dates to format correctly. Always use the *.xlsx version to make updates to retain required date formatting.
Some CSV imports include date fields, particularly 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. This may not be yyyy-mm-dd.
This means that even if your CSV file is correct (open it in Notepad or WordPad to see the raw data), Excel will display the reformatted date field incorrectly. After you make changes and save the CSV file, Excel will apply the incorrect date format to the fields.
This is bad because your CSV import will no longer work.
Do not 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 Command + 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 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.
To confirm the CSV is correct, open it in Notepad or WordPad and check the date. If you changed the date format in Excel to yyyy-mm-dd and saved it as a CSV, the date will appear in this format in the raw CSV file. Do not use Excel to check it; Excel will convert the dates back to the system default format.