Concatenate Date and Time into Data Loader Format

There are a number of ways to do this, here are two ways I have found that work well....

If you format something as TEXT in excel you can format it right in the formula. For example when working with dates and times that are separate:

          =CONCATENATE(TEXT(A2,"yyyy-mm-ddT"),TEXT(B2,"hh:mm:ss.000")," EST")

Would give you the proper Data loader import format when A2 is the date and B2 is the time.


Another way to do this is to just add the two columns and format them like this then format the results:

          =A2+B2

          Format -> Custom -> Type: yyyy-mm-ddThh:mm:ss.000 "EST"


I have found both ways work well, but it will depend on how the data is formatted to make the decision on which one works best.

Datetime Revisited

My first post was about Datetime fields used with Data Loader. I recently had to import these and noticed that I did not use the proper format in my example, below I have made the necessary changes.

Also I added in the timezone, this will cause headaches if you do not specify the timezone. The format below worked out perfectly for me.

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

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!