Salesforce Date Format for Data Import

When using the data loader or any other data import tool in Salesforce, one thing that always hold a smooth insert or update of records is the format of the date value that is being inserted or updated on the date field.

Strangely enough, if you first extract data from salesforce in excel. Change some values in the excel and then try to load the same file back into salesforce which has some date fields, it will fail saying ‘Invalid Date Format’.

Why does it happen?

The Data Loader automatically converts the date mentioned in the CSV files to GMT. If the time zone in your system has not been set or if the clock sets itself to the daylights savings time (DST), there are chances of your dates being inconsistent by a day.

What is the required Date Format for Data Load?

Salesforce accepts the following Date formats in the Date or DateTime fields. This field supports the following variations that include a time stamp as well:

Date Field Format:

• YYYY/MM/DD
  • Example: 2021/12/01
• YYYY-MM-DD
  • Example: 2021-12-01

DateTime Field Format:

• YYYY-MM-DD hh:mm:ss
  • Example: 2021-12-01 00:00:00
• YYYY-MM-DDThh:mm:ssZ
  • Example: 2021-12-01T00:00:00Z
• YYYY-MM-DDThh:mm:ss.sssZ
  • Example: 2021-12-01T00:00:00.000Z

Note: ‘Z’ denotes the timezone of the hh:mm:ss in the above formats.

How to convert to correct Date Format in Excel?

In order to correct the Date format in an extracted excel file, simply follow the following steps:

1. Open the extracted file in Microsoft Excel.
   • 
2. Right-click the cell where you entered the dates and click on “Format Cells.”
   • 
3. Click on Date.
   • 
4. In Type, scroll down to the format 2012-03-14, select it and click OK.
   • 

In order to correct the DateTime format in an extracted excel file, simply follow the following steps:

1. Open the extracted file in Microsoft Excel.
   • 
2. Right-click the cell where you entered the dates and click on “Format Cells.”
   • 
3. Click on Custom.
4. In Type, enter yyyy-mm-ddThh:mm:ss.sssZ.
   • 
5. For example, if the cell showed 10/12/2021 9:00, the date will now be 2021-12-10T09:00:00.00Z.
   • 

Conclusion

Salesforce date format is important to remember when loading data into salesforce and also ensure when converting to the right format then the values don’t change by a day or two due to time zone differences.