Converting Datetime in Excel to a format Data Loader recognizes

I have had a number of clients that would like to move activities from their old CRM over to Salesforce. Bringing  these activities or calendar events over as Events into SF has given me all sorts of trouble recently. The problem lies in the Datetime fields that are imported into SF such as ActivityDateTime, StartDateTime and EndDateTime which the customer usually needs as this contains the not only the date, but the time of the event as well.

For these imports I always use the APEX Data Loader and the files I use to import are in Excel CSV format. So I have to be sure that the CSV file is formatted properly before importing everything into SF.

Most often the datetime will be exported out of the old CRM in a format similar to this

8/19/2005 9:30

I have found that Data Loader is very specific about how it receives a datetime. If you try to import the format above into a datetime field with Data Loader, it will not only not load, but it will not appear in the error file, it simply skips over the record all together.

The formats Data Loader needs in order to work properly as described in the Data Loader Developer's Guide are:

We recommend you specify dates in the format yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm:

• yyyy is the four-digit year

• MM is the two-digit month (01-12)

• dd is the two-digit day (01-31)

• HH is the two-digit hour (00-23)

• mm is the two-digit minute (00-59)

• ss is the two-digit seconds (00-59)

• SSS is the three-digit milliseconds (000-999)

• +/-HHmm is the Zulu (UTC) time zone offse

• yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

• yyyy-MM-dd'T'HH:mm:ss.SSS Pacific Standard Time

• yyyy-MM-dd'T'HH:mm:ss.SSSPacific Standard Time

• yyyy-MM-dd'T'HH:mm:ss.SSS PST

• yyyy-MM-dd'T'HH:mm:ss.SSSPST

• yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00

• yyyy-MM-dd'T'HH:mm:ss.SSSGMT-08:00

• yyyy-MM-dd'T'HH:mm:ss.SSS -800

• yyyy-MM-dd'T'HH:mm:ss.SSS-800

• yyyy-MM-dd'T'HH:mm:s

• yyyy-MM-dd HH:mm:ss

• yyyyMMdd'T'HH:mm:ss

• yyyy-MM-dd

• MM/dd/yyyy HH:mm:ss

• MM/dd/yyyy

So, in order to accommodate this stringent formatting that is required for a Datetime, I had to create a custom format in Excel. Here is what I did...

• Right Click on the Datetime field and select Format Cells
• Under Category select Custom
• Under Type: paste this text " yyyy-mm-ddThh:mm:ss.SSS "EST" "
• Click OK

Alternatively if the date and time are in different columns in the spreadsheet you can use a formula like to to bring them together:

=TEXT(YEAR(D2),"0000")&"-"&TEXT(MONTH(D2),"00")&"-"&TEXT(DAY(D2),"00")&"T"&TEXT(HOUR(E2),"00")&":"&TEXT(MINUTE(E2),"00")&":00.000 EST"

This format will load cleanly into SF with Data Loader!

In order to apply that formatting to the other cells (I know there are probably 700 different ways to do it with excel) just right click and copy the cell that was just formatted and highlight all of the other cells that you need to apply the formatting to then right click and select Paste Special and select the radio button Formats then click OK. 

Then you are good to go!