Ian Jamieson

Have you ever uploaded (inserted, updated or exported) dates into records in Salesforce but you then notice the date is not quite what you expected when you look at the dates in the Salesforce front-end? Or the date/time you have uploaded is a day or a few hours out? For example, you upload the date 02/01/2024 (dd/mm/yyyy) but on Salesforce you see 01/01/2024.

This is because when the data is inserted, updated, or exported the values will always be in Greenwich Mean Time (GMT) time and are not translated to the running user’s timezone.

This can be very frustrating.

To help get around this issue I recommend that before uploading the dates data you format the dates into what is called Military Time Zone format. This format is:

yyyy-mm-ddThh:mm:ss.sssZ

The z at the end is crucial. Using this approach, Data Loader will show the date value the same as what is stored in Salesforce.

Setting the format to Military Time Zone format needs to be done in Excel before making the upload or insert. Here’s how to do it:

1. Open the CSV file that you want to upload/insert in Microsoft Excel.
2. Right-click on the cell where the dates are stored and click on “Format Cells.”
3. Click on Custom.
4. In Type, enter yyyy-mm-ddThh:mm:ss.sssZ. For example, if the cell showed 7/7/2017 8:00, the date will now be 2017-07-07T08:00:00.00Z.
5. Save the CSV File.
6. Process to Data Loader and make the upload using the CSV file that you have prepared in a test environment. It’s crucial that you always test first.
7. Once you are happy with the test upload and the dates are showing in Salesforce as you want them, then proceed to upload in the Production environment of Salesforce.

If you have any further comments or recommendations please tell me in the comments below.