Salesforce Date and DateTime Format (With Excel CSV Fixes)

Salesforce Date fields expect YYYY-MM-DD (for example 2024-06-02). DateTime fields expect ISO 8601 with a time zone, such as 2024-06-02T14:30:00Z for UTC or 2024-06-02T14:30:00+12:00 with an offset. When a date import fails or loads the wrong day, the cause is usually the CSV file. Excel rewrites dates into your computer's locale, or into serial numbers, when you open and save the file.

Here is what Salesforce expects and what usually breaks:

• Date field (for example CloseDate or Birthdate): use 2024-06-02. With the API and Bulk API 2.0, a date field does not take a time or an offset.
• DateTime field (for example a custom Last_Contact__c): use 2024-06-02T14:30:00Z or 2024-06-02T14:30:00+12:00. Milliseconds are optional and limited to three digits (.000). Salesforce stores DateTime in UTC and shows it in each user's time zone.
• DateTime with no time zone (2024-06-02 14:30:00): the load tool assumes one. Data Loader uses its Time Zone setting, or your computer's time zone if that is blank, so times can move by hours.
• Excel serial number (45445): this is 2 June 2024 stored as a number because the column is formatted as General or Number. Salesforce can't read it. Convert it with =TEXT(A2,"yyyy-mm-dd").
• US format (06/02/2024): Data Loader reads MM/dd/yyyy by default, so this loads as 2 June 2024.
• AU/NZ/UK format (02/06/2024): Data Loader also reads this as MM/dd/yyyy, so it loads as 6 February unless you tick Use European date format in Data Loader settings. Days above 12, like 13/06/2024, will not parse as a month. The Data Import Wizard reads dates in the importing user's Salesforce locale instead.
• Short or two-digit years (2/6/24): ambiguous in every tool. Convert to 2024-06-02.
• Dates landing one day early (common in NZ and AU): a Date value converted through local midnight to UTC can move back a day when the machine is east of GMT. Salesforce's Data Loader guide recommends using GMT in that case, so set Data Loader's Time Zone to GMT for date-only loads.
• Re-opening the CSV in Excel: Excel converts 2024-06-02 back to your local short date when you save. Make the date column the last edit before you load, or edit the CSV in a text editor.

1. Salesforce Date and DateTime formats

The two formats to use are:

• Date: YYYY-MM-DD (for example 2024-06-02)
• DateTime: YYYY-MM-DDThh:mm:ssZ (for example 2024-06-02T14:30:00Z)

These formats avoid the day and month mix-ups that locale-based formats cause, and they mean the same thing in every time zone.

2. DateTime fields: ISO 8601, UTC and user time zones

A DateTime field holds a date and a time, such as CreatedDate or a custom Last_Contact__c. Send it in ISO 8601 format, YYYY-MM-DDThh:mm:ss, followed by either Z for UTC or a numeric offset from UTC. Some examples:

• 2024-06-02T14:30:00Z: 2:30 PM UTC on 2 June 2024.
• 2024-06-02T14:30:00+12:00: 2:30 PM New Zealand Standard Time. Salesforce stores it as 2024-06-02T02:30:00Z.
• 2024-06-02T09:00:00-07:00: 9:00 AM Pacific Daylight Time. Salesforce stores it as 2024-06-02T16:00:00Z.
• 2024-06-02T14:30:00.000Z: the first example with milliseconds. Data Loader's recommended format is yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm, so 2024-06-02T14:30:00.000+1200 also works there.

Salesforce stores every DateTime value in UTC, and the API transfers DateTime values in UTC. The Salesforce UI then shows each value in the viewing user's own time zone. A value stored as 2024-06-02T02:30:00Z appears as 2:30 PM on 2 June to a user in Auckland and 7:30 PM on 1 June to a user in Los Angeles. Both users are looking at the same moment, so a report that seems a few hours or a day out is often correct.

Data Loader has two settings that change how it reads DateTime values:

• Time Zone: used when a value has no time zone, such as 2024-06-02 14:30:00. You can enter a name like Pacific/Auckland or an ID like GMT+12:00. If you leave it blank, Data Loader uses your computer's time zone, and an invalid value falls back to GMT.
• Use European date format: makes Data Loader read dd/MM/yyyy and dd/MM/yyyy HH:mm:ss instead of the default MM/dd/yyyy and MM/dd/yyyy HH:mm:ss.

Adding Z or an offset to every value means neither setting can shift your times. The full list of formats Data Loader accepts is in Salesforce's Data Loader data types guide.

3. Best practices for date imports

• Stop Excel changing your dates: before you paste or type dates, select the column and choose Format Cells > Text, so Excel keeps 2024-06-02 exactly as typed. If the column already holds Excel dates, use Format Cells > Custom with yyyy-mm-dd (or yyyy-mm-dd"T"hh:mm:ss"Z" for UTC DateTime values), then save as CSV. Excel writes the displayed value to the CSV, so the file gets the Salesforce format.
• Consistent time zones: Use UTC where you can. For DateTime fields, add Z at the end to mark UTC (for example 2024-06-02T14:30:00Z). If you need a local time, use a numeric offset, for example 2024-06-02T14:30:00-08:00 or 2024-06-02T14:30:00+12:00.
• Data validation: Before importing data, validate the date format using tools or scripts. This step can help catch errors that might cause import failures or data corruption.
• Template usage: Use an import template so every file arrives with dates in the same format.
• Automated conversion: Convert dates with your import tool or a script so every row is handled the same way.
• Error handling: Keep the error file from every load and check it for date errors before you re-run. See 10 common Salesforce data loading errors.
• Check your IDs too: the same Excel save that breaks dates can break lookups. Use 18-digit Salesforce IDs in any CSV you edit in Excel.

4. Example: loading a Date of Last Contact column

Say you are importing a CSV of customer data with a Date of Last Contact column that maps to a custom Date field, Last_Contact_Date__c. Before you load:

1. Format check: Make sure every value in the column is YYYY-MM-DD.
2. Template: Use an import template with the column already set up for Salesforce's date format.
3. Automated conversion: Convert any dates that aren't in the right format with your import tool or a script.
4. Validation: Check a sample of rows, including any day above 12, before starting the full import.

Following these steps means the dates in Salesforce match the dates in your source file. If you are loading historical CreatedDate values, see importing audit fields. If you need to migrate records from one Salesforce org to another, the source org returns DateTime values in UTC, so keep the Z on each value when you load them into the target org. Date formats are also one line on our Salesforce data migration checklist.

5. Converting date formats in Dataimporter

In Dataimporter you pick the date format your source file uses, and it converts values to the format Salesforce expects. You can also use Dataimporter Formulas to reformat dates or add a time zone before the load.

6. Summary

Use YYYY-MM-DD for Date fields and ISO 8601 with Z or an offset for DateTime fields, and convert dates as the last step before you load so Excel can't change them again.

Frequently asked questions

What date format does Salesforce use for imports?

Date fields take YYYY-MM-DD, for example 2024-06-02. DateTime fields take ISO 8601 with a time zone, for example 2024-06-02T14:30:00Z for UTC or 2024-06-02T14:30:00+12:00 with an offset. Data Loader also accepts MM/dd/yyyy, and dd/MM/yyyy when Use European date format is ticked, but YYYY-MM-DD works the same in every tool.

Why does Excel change my Salesforce dates?

Excel converts anything that looks like a date into its own date value, then shows or saves it in your computer's locale format. Saved as a CSV, 2024-06-02 can become 2/06/2024 or 6/2/2024, and a column formatted as General can become a number like 45445. Format the column with =TEXT(A2,"yyyy-mm-dd"), save, and don't reopen the CSV in Excel before loading.

How do I import DD/MM/YYYY dates into Salesforce?

The safest option is to convert them to YYYY-MM-DD first. In Data Loader you can instead tick Use European date format in Settings, which makes it read dd/MM/yyyy. The Data Import Wizard follows the importing user's locale, so a user set to English (New Zealand) or English (Australia) can import dd/MM/yyyy, but a US-locale user cannot.

Why are my DateTime values off by a few hours after import?

Either the value had no time zone and the load tool assumed a different one, or you're comparing UTC with your local time. Salesforce stores DateTime in UTC and shows it in each user's time zone. Add Z or a numeric offset such as +12:00 to every value so there is nothing to guess.

Why did my dates import one day early?

This usually happens east of GMT, including NZ and AU. The date is treated as local midnight, converted to UTC, and lands on the previous day. Salesforce recommends using GMT in this case: set Data Loader's Time Zone setting to GMT for date loads, or use a tool that sends Date fields as plain YYYY-MM-DD.