Company Blog Support
Frank Hagan
posted

Contents

    Back to Top

    Importing Contacts: Formatting Dates for Import

     

    About Importing Contact Field Dates

    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
    1. Make sure your dates are in text format. Highlight the cells, right click, and select Format Cells > Text

      Change date to US format 1

    2.  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. 

      Change date to US format 2

    3. Highlight your date column and select the Data tab. Click Text to Columns. Select the radio button Delimited and click Next

      Change date to US format 3

    4. 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. 

      Change date to US format 4

    5. 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.

      Change date to US format 5

    6. Click Finish. Each column will now have the values for the day, month and year filled in. 

    7. 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.

      Change date to US format 6

    8. 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. 

      Change date to US format 7

    9. 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.

    10. 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. 

     

    Articles in this section

    Have more questions? Submit a request

    Comments

    Powered by Zendesk