How to Save Your CSV Dates Before Excel Does
A simple double-click can turn a UK spreadsheet into a transatlantic mess. You export a clean CSV from a UK system, open it in US Excel, and suddenly 28/05/2026 becomes May 28—or worse, April 28. The file isn’t damaged; Excel is just guessing your date format based on your computer’s locale.
Why Excel Rewrites Your Dates
CSV files are plain text; they don’t carry formatting clues. When you open one by double-clicking, Excel parses every value using your operating system’s regional settings. US Excel expects MM/DD/YYYY, while UK Excel favors DD/MM/YYYY. Dates like 03/04/2025 are especially risky because both parts are below 12. US Excel reads it as April 3; UK Excel reads it as March 4. No warning appears, and once Excel converts the value, the original string is lost.
Other common pitfalls include serial numbers masquerading as dates (44927), two-digit years triggering century assumptions, and re-saving the file as CSV which erases any fixes you made.
The Right Way to Open a CSV
Stop double-clicking CSVs. Instead, open Excel first and use the proper import path. Go to Data → Get Data from Text/CSV (older versions: Data → From Text), then explicitly set date columns to Text if you need to preserve the original string. Confirm the source region and convert to a single, agreed-upon format before sharing the data.
For team exchanges, agree on ISO 8601 (YYYY-MM-DD). It’s unambiguous, sorts correctly as text, and works across JSON APIs and databases. If you’re already seeing misread dates, a free browser tool called FormatList can repair them without uploading your file. Its Date Format Fixer highlights ambiguous rows and lets you export ISO, US, or UK formats. You can also run an Ambiguous Date Checker for critical entries like contracts or visa forms.
Fix dates before converting to JSON or feeding them into a pipeline; once baked into JSON, bad dates propagate through your entire workflow.
Source: DEV Community. AI-assisted editorial synthesis — TechnoExpress.