4

I have a custom date/Time field in salesforce for a object and i am trying insert values for that field through the data loader.I have used yyyy:mm:dd hh:mm:ss date format in excell. User time zone and company default time zones are same.But when i insert data to that field it's showing date as data as given date -1 day.This is the sample data enter image description here

But in salesforce it is stored as enter image description here and enter image description here

What should i do to store the exact date from excell?

enter image description here

1
  • yyyy-mm-ddThh:mm:ssZ
    – Nick C
    Sep 2, 2016 at 7:52

4 Answers 4

8

You want to format it as:

yyyy-mm-ddThh:mm:ssZ

Where you can just make hh:mm:ss be 00:00:00.

e.g. 2016-01-18T00:00:00Z

3
  • HI Nick,Thanks for your response .I tried your format but still same result Sep 2, 2016 at 9:44
  • I believe Data Loader is just looking for SQL-formatted datetimes. Tried a bunch of formats, but that's the only one that didn't throw parsing errors. For example, today would be 2020-10-08 18:57:48.
    – cdwyer
    Oct 8, 2020 at 23:57
  • Format the 'Date' and 'Date Time' data in a CSV file For Salesforce Data Loader - help.salesforce.com/s/articleView?id=000385167&type=1 Sep 21, 2023 at 17:37
1

Some versions of Excel don't have date-time formats available in the standard pick lists, but you can just enter a custom format string such as yyyy-mm-dd hh:mm:ss by:

Right click -> Format Cells Number tab Choose Category Custom Enter your custom format string into the "Type" field This works on my Excel 2010

4
  • I get the correct format in excell but when i insert data it goes one day behind. Sep 2, 2016 at 9:57
  • have you checked Language & Time Zone of your salesforce org. just go through your name --> setting--> personal-->Language & Time Zone Sep 2, 2016 at 10:07
  • I have posted that in my question.Is is something i should do with time zone? Sep 2, 2016 at 10:09
  • Sorry my mistake... Just put this time zone (GMT-07:00) Pacific Daylight Time (America/Los_Angeles). hope so its work Sep 2, 2016 at 10:12
1

Two things about loading date types:

  1. Data loader ignores the HH:MM:SS portion of Date types, so you can just write it as YYYY-MM-DD. Reserve the HH:MM:SS for Datetime types.

  2. Data loader interprets all dates as GMT. When you look at it in the UI, you are looking at the local time which is MT(GMT-6).

Date '2016-01-18' in the CSV, after the load it becomes '01/17/2016 18:00:00' in the UI.

-1

Data import wizard requires Datetime to be in "mm/dd/yyyy H:MM AM/PM" format.

Also make sure that timezone set for your org is same as your machine's.

3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .