Data cleaning for CRM imports

Salesforce import errors

Fix Salesforce Data Loader date format errors (and dates off by one day)

Two separate things go wrong with dates in Data Loader. First, it expects a defined format, so a text label or an ambiguous slash date gets rejected as an invalid date. Second, it reads date values in GMT, so a date written at midnight local time can land a day early. The first is a file problem you fix by standardizing the column; the second is a time zone setting inside Data Loader. Knowing which is which saves the guesswork.

Standardize your date column in Sift, free →

What goes wrong

The first and third rows are format problems in the file. The middle row is the off-by-one-day gotcha, and it happens even when the text looks perfectly correct.

Why it happens

• Data Loader expects a defined format. Date fields want a pattern like yyyy-MM-dd, and date/time fields want yyyy-MM-ddTHH:mm:ss.SSSZ. Anything outside the pattern is treated as an invalid date.
• Ambiguous dates are read by locale. 07/05/2026 could be 7 May or 5 July. Data Loader resolves dd/mm versus mm/dd by the machine's settings, so the same file can import differently on two computers.
• The off-by-one-day GMT gotcha. Data Loader interprets date and date/time values in GMT/UTC. A date at midnight in your local time zone shifts back a day when it is read in UTC, so the value you see is not the value that lands in Salesforce.

The manual fix

1. Format the whole date column to the exact pattern Data Loader wants, yyyy-MM-dd for a date field or yyyy-MM-ddTHH:mm:ss.SSSZ for a date/time field, so nothing is rejected as an invalid date.
2. Resolve any ambiguous slash dates before loading, so 07/05 isn't left for the locale to guess.
3. To avoid the day shift, per xappex guidance, either include an explicit time and time zone with each value, or set Data Loader's own time zone setting to match the time zone your dates are written in.
4. Load a small test batch first and confirm the dates that land in Salesforce match the dates in your file, before running the whole set.

The Sift fix

1. Drop your CSV or Excel file into Sift. It runs in your browser; the file is never uploaded.
2. Sift standardizes the whole date column to an unambiguous format like YYYY-MM-DD, or the exact pattern your import needs, so text labels and mixed formats become one clean shape.
3. Sift previews the ambiguous rows, the ones where day and month are both 12 or under, so you decide 01/02 once for the whole file instead of leaving it to the locale.
4. Review every change with a before/after diff, then export the clean file straight to Data Loader.

What Sift can't fix

Honesty matters here: Sift fixes the date format in your file, so Data Loader stops rejecting rows and stops guessing ambiguous ones. It cannot fix the off-by-one-day shift, because that is a time zone interpretation setting inside Data Loader itself, not something in your file. Sift can't change how Data Loader reads UTC. So alongside a clean format, set Data Loader's time zone correctly or include an explicit time with each value, and the dates that land in Salesforce will match the ones you exported.

Related guides