NOTE: This article currently only applies when importing data that contains a date field. At the moment, this only applies to importing TODOs (because of the DueDate field).
Expected Date Format:
When importing data from Excel .CSV (Comma Separated Values) files into ClearEvent, it's important to format dates in your .CSV import file as YYYY-MM-DD to avoid errors. If you don't provide the expected date format, corrupted dates may be imported and will later need to be manually fixed in ClearEvent.
Excel & Date Formatting Challenges:
Excel often uses your computer's regional date/time settings to format dates when it saves the .CSV file. This means the date format you see in Excel is often not what gets saved to the .CSV file. Because ClearEvent requires a consistent date format to accurately import your data, we need to make sure all dates are in the format: YYYY-MM-DD.
Fear not! We've outlined a few simple steps below to explain how to do this.
Formatting Date Columns In Excel:
Please follow these steps each time a .CSV file is opened and edited in Excel prior to saving your changes to .CSV.
- Get a .CSV template from any Import dialog in the Event Manager App.
- Using Excel, open the .CSV template file.
- Edit the template and paste in the data you wish to import.
- Before you save the .CSV file, locate each date column (e.g. DueDate).
- Right-click each date column header to select the entire column and choose Format Cells...
- From the Format Cells dialog, click the Number tab and choose Custom. Enter "yyyy-mm-dd" into the Type input field and click the OK button:
At this point, the .CSV file should contain the correct date format and is ready to import.
Verifying Date Formats In Your CSV File:
Excel can be deceiving when you are trying to inspect date formats. The best way to verify that your dates are in the correct format is to open the .CSV file using a plain text editor like Notepad, TextPad, TextEdit, etc...
Once you've opened the file in your favorite editor, look for the date column and verify the date is in the format YYYY-MM-DD.
Here's an example of a valid date in a .CSV file that will import with no problem: