Importing Contacts: Formatting Dates for Import

About Importing Contact Field Dates

Applies to: ONTRAPORT Plus and above.

ONTRAPORT accepts dates in the US "MM/DD/YYYY" format only. Use this guide to quickly convert dates in the European / Australian format of "DD/MM/YYYY" for importing.

Using Excel or Other Spreadsheet Programs

Make sure your dates are in text format. Highlight the cells, right click, and select Format Cells > Text.
Add four empty columns next to the date column. Select the column next to your date column, right click and select Insert. Choose to insert an Entire column. Repeat this process three more times until you have four blank columns next to your date.
Highlight your date column and select the Data tab. Click Text to Columns. Select the radio button Delimited and click Next.
In step 2 select the Other check box and insert the character used to separate dates in your CSV file (usually a "/" or "-" character). Uncheck the other boxes.
In step 3, select all three of the columns and check the Text radio button. Make sure you edit the Destination to the first of the empty columns you created.
Click Finish. Each column will now have the values for the day, month and year filled in.
Right click on the empty fourth column and select Format. Select Date and choose English (United States) in the Locale drop down. Select the format for M/D/YYYY as shown.
In the first cell to hold your new US format date enter an equals sign, then type the cell reference for that row's month field (K2 in the example below). Type &"/"& and then the cell reference for that row's day field (J2). Type &"/"& again and type the cell reference for that row's year field.
Press ENTER and verify the format is correct in the MM/DD/YYYY format. Copy the formula to the rest of the cells in the column.
Save the file as a CSV file for import into ONTRAPORT. During the import, use the US format column for your date import, and ignore the original date and day, month and year fields.