How to resolve uk vs us date format conflicts in excel crm exports
- Step 1Export from your CRM — Download the Excel/CSV export containing the mixed regional date columns (e.g.
created_at,last_contact). - Step 2
- Step 3Decide whether your column is day-first — If a column is consistently UK day-first, spot-check a few
0X/0Y/YYYYrows after the run — chrono reads those as month-first. Consistently US or ISO columns convert cleanly. - Step 4Name the date columns (optional) — Type headers comma-separated to restrict the scan, or leave blank to auto-detect across the export.
- Step 5Pick ISO 8601 and run — ISO 8601 is the unambiguous target. Run to produce
dates-standardized.xlsx. - Step 6Reconcile against the findings report — Review
failedCellsfor unparseable statuses and sanity-check any low-number slash dates in a known day-first column.
How the parser resolves UK/US slash dates
The decisive rule, verified by running chrono-node: when the first number cannot be a month (>12) it is treated as a day; otherwise month-first wins. There is no locale option to override this.
| Input | First number | chrono reads it as | ISO output | Matches UK intent? |
|---|---|---|---|---|
06/07/2024 | 6 (<=12) | Month 6, day 7 | 2024-06-07 | No (UK meant 7 July) |
13/07/2024 | 13 (>12) | Day 13, month 7 | 2024-07-13 | Yes |
25/12/2024 | 25 (>12) | Day 25, month 12 | 2024-12-25 | Yes |
12/06/2024 | 12 (<=12) | Month 12, day 6 | 2024-12-06 | No (UK meant 6 Dec) |
2024-07-06 | 2024 | Year-first ISO | 2024-07-06 | Yes (already unambiguous) |
6 July 2024 | n/a | Spelled month | 2024-07-06 | Yes (text removes ambiguity) |
What chrono-node does with common date strings
Behaviour verified by running the bundled chrono-node parser. The parser uses its default English settings — month-first for ambiguous slash dates — and there is no locale switch in this tool.
| Input cell value | Parses? | Result (to ISO 8601) | Note |
|---|---|---|---|
2024-09-29 | Yes | 2024-09-29 | Already ISO — passes through unchanged |
09/07/2024 | Yes | 2024-09-07 | Read as month-first (US): Sep 7, not 9 July |
13/07/2024 | Yes | 2024-07-13 | Day-first used only because 13 cannot be a month |
29 September 2024 | Yes | 2024-09-29 | Natural-language text date |
29-Sep-24 | Yes | 2024-09-29 | Two-digit year is expanded |
2024-09-29 14:30 | Yes | 2024-09-29 | Time is parsed then dropped by the date-only token |
next friday | Yes | (date relative to today) | Relative phrases resolve against the run date |
Q3 2024 | No | (left unchanged) | Listed in the findings report |
45899 (number as text) | No | (left unchanged) | A bare serial typed as text does not parse |
Tier limits for the Date Standardizer
The Date Standardizer is gated to Pro and above — a Free account cannot run it. Limits below are the real Excel-family numbers. One file is processed per run.
| Tier | Max file size | Max rows | Runs this tool? |
|---|---|---|---|
| Free | 5 MB | 10,000 | No — Pro required |
| Pro | 50 MB | 100,000 | Yes |
| Pro-media | 200 MB | 500,000 | Yes |
| Developer | 500 MB | Unlimited | Yes |
Cookbook
Real CRM column scenarios. Customer names are anonymised. The takeaway: spelled-out months and any date with a number above 12 are safe; bare low-number slash dates inherit chrono's month-first default.
Unambiguous dates convert perfectly
When the day value is above 12 there is no conflict to resolve — chrono gets these right every time regardless of UK or US origin.
Before (column: last_contact) After (ISO 8601) ---------------------------- ---------------- 25/12/2024 2024-12-25 13/07/2024 2024-07-13 2024-09-29 2024-09-29 31/01/2025 2025-01-31
The ambiguous case to watch
When both numbers are 12 or under, chrono picks month-first. If this column is actually UK day-first, the output will be wrong — and it is NOT flagged, because it parsed successfully.
UK rep intended: chrono produced (month-first): 06/07/2024 = 7 July 2024-06-07 = 7 June 03/04/2024 = 3 April 2024-04-03 = 3 April? no: April 3 Lesson: spot-check low-number slash dates in known day-first columns.
Make the source unambiguous first
If you control the export, switching the CRM's date display to spelled-out months or ISO before exporting removes all ambiguity — then the standardizer is purely cosmetic.
CRM export set to long dates: 7 July 2024 3 April 2024 Standardizer (ISO 8601): 2024-07-07 2024-04-03 No month/day guessing involved.
Restricting the scan to specific CRM fields
CRM exports often carry numeric IDs and amounts that can look date-ish. Name the real date columns to keep auto-detect from touching anything else.
Columns box: created_at, last_contact, renewal_date Result: only those three rewritten. "deal_value" (e.g. 12.07) and "account_id" untouched.
Free-text statuses are reported, not converted
CRM date fields frequently hold placeholders. These don't parse and are surfaced in the findings report for cleanup.
Before (column: renewal_date) Q4 2024 Pending 01/03/2025 After Q4 2024 <- unchanged Pending <- unchanged 2025-01-03 <- converted (month-first: Jan 3) findings.failedCells: Row 2, renewal_date: "Q4 2024" Row 3, renewal_date: "Pending"
Edge cases and what actually happens
Both numbers <= 12 in a day-first column
Silently wrongchrono picks month-first, so a UK 06/07/2024 (7 July) becomes 7 June. It parses successfully, so it is NOT in the findings report. Spot-check these manually — the tool has no way to know the intended locale.
Day value above 12
Resolved correctly13/07/2024, 25/12/2024 and similar are unambiguous: the >12 number can only be a day, so the date is correct regardless of region.
Mixed UK and US rows in the same column
Inconsistent riskIf the same column genuinely contains both conventions for low-number dates, no single rule can disambiguate them. Split the export by source rep, or fix at the CRM before exporting.
ISO or spelled-out dates anywhere in the column
Safe2024-07-06 and 6 July 2024 carry no ambiguity and always convert to the intended date.
Looking for a UK-first toggle
Not availableThere is no locale/day-first option in this tool, and no per-cell ambiguity flag. If you need guaranteed day-first parsing, fix the format at source first.
Customer PII in the export
Stays localAll processing is in-browser; the CRM export with its PII is never uploaded. For redacting that PII, see the email/phone scrubber linked in the FAQs.
Quarter or season placeholders
Failed parseQ3 2024, Spring, Pending and similar are left unchanged and listed in failedCells.
Two-digit years from legacy CRM
Expanded06/07/24 expands the year to 2024 (still month-first for the day/month). Verify the century for old records.
Time-stamped contact dates
Time dropped06/07/2024 09:15 parses but the time is discarded by the date-only output formats.
Export exceeds tier limit
RejectedFiles above 50 MB / 100,000 rows (Pro) are refused before processing; upgrade to Pro-media/Developer or split the export.
Frequently asked questions
How does the tool decide if `06/07/2024` is June 7 or 7 July?
It uses chrono-node's default English mode, which is month-first. So 06/07/2024 becomes June 7. Only when the first number exceeds 12 (e.g. 13/07/2024) does it switch to day-first, because 13 can't be a month.
Can I force all dates to be read as UK day-first?
No. This tool has no locale toggle. If a column is consistently UK day-first with low-number dates, the safest fix is to change the date display to spelled-out months or ISO in your CRM before exporting.
Will it flag the ambiguous cells for me?
No. An ambiguous 06/07/2024 parses successfully (just possibly with the wrong meaning), so it is not added to the findings report. Only values that fail to parse at all are flagged. Spot-check low-number slash dates yourself.
Which dates are always safe?
Any date where a number is above 12 (25/12/2024), any ISO date (2024-07-06), and any spelled-out date (6 July 2024). These have no month/day ambiguity.
What output format should I use for a data warehouse?
ISO 8601 (yyyy-MM-dd). It is unambiguous, sorts correctly as text, and is the default.
Does it fix Excel serial-number dates too?
Yes, when they are true Excel date cells — SheetJS reads them as Date objects and reformats them directly, with no month/day guessing.
Is the CRM export uploaded to a server?
No. It is processed entirely in your browser, which is important given CRM exports usually contain customer PII.
Can I limit the scan to just the date fields?
Yes. Type the date column headers into the comma-separated box (e.g. created_at, last_contact). Leave it blank to auto-detect.
What about placeholders like `Pending` or `Q4`?
They don't parse as dates, so they are left unchanged and listed in the findings report's failedCells.
Are the outputs real Excel dates?
No, they are text strings. ISO 8601 text sorts chronologically, but for Excel date math you'd re-convert the column in Excel afterward.
How do I redact the PII before sharing the standardized file?
Run the standardized file through the email and phone scrubber to redact PII before sending it on.
What if some rows have times?
They parse, but the six output formats are date-only, so the time is dropped.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.