Data cleaning for CRM imports
Dates
CSV date format for a CRM import (dd/mm vs mm/dd, fixed once)
A CSV stores no date type. It is plain text, so 07/05/2026 could mean 7 May or 5 July depending on who reads it and which locale their machine is set to. A CRM import wants one clear, consistent format for the whole column, and if your file mixes orders (or hides Excel serial numbers) the import either fails or files dates on the wrong day. The fix is to standardize the column once, before it goes anywhere near the import screen.
Fix your date column in Sift, free →
The ambiguity problem
Three kinds of trouble hide in a date column. The first is genuinely ambiguous, the second is Excel damage, and the third is simply broken:
Only the first row is truly undecidable from the value alone. The Excel serial number is fully recoverable, and the impossible date tells you the whole column's day/month order is swapped.
The manual fix
- Rebuild the value with Excel's TEXT and DATE functions, for example splitting the string and reassembling it as YYYY-MM-DD, so the order is spelled out and can't be re-guessed.
- Watch your machine's locale. The same formula produces different results on a US versus a UK machine, and opening the CSV before you convert can already have flipped some rows. Do the conversion on the file, not on your assumptions.
- Choose the matching format in the import wizard. Even a clean column still needs you to tell the CRM which order it is in, so select the date format that matches your file at the import step, every time.
The Sift fix
- Standardize the whole column to one unambiguous format, either YYYY-MM-DD or the exact pattern your CRM's import template needs, so every row reads the same way.
- Convert Excel serial numbers back into dates, so a value like 45784 becomes a real date instead of an integer the CRM rejects.
- Preview the ambiguous rows, the ones where day and month are both 12 or under, like 01/02. Sift shows them to you so you decide the order once, for the whole file, instead of leaving it to whatever locale the CRM happens to use.
- Review with a before/after diff so you can see exactly what changed in each row before you commit, then export a clean CSV for the import.
What Sift can't fix
Two honest limits. A single, genuinely ambiguous date with no other signal, a lone 01/02 in a column where nothing else disambiguates the order, cannot be known from the value, so Sift shows it to you to decide rather than guessing and quietly writing the wrong day. And Sift cannot reach into the CRM: it standardizes your column and exports a clean file, but you still select the matching date format in the CRM's import step yourself. Sift makes that selection easy by giving you a column that is already in one clear, consistent format.