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

  • yyyy-mm-ddThh:mm:ssZ
    – Nick Cook
    Sep 2 '16 at 7:52

You want to format it as:


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

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

  • HI Nick,Thanks for your response .I tried your format but still same result Sep 2 '16 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 '20 at 23:57

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

  • I get the correct format in excell but when i insert data it goes one day behind. Sep 2 '16 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 '16 at 10:07
  • I have posted that in my question.Is is something i should do with time zone? Sep 2 '16 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 '16 at 10:12

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.


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.