Excel date format CSV wrong (DD/MM vs MM/DD)
A CSV file contains the date 03/04/2026. Is this March 4th (US format) or April 3rd (UK/European format)?
If you open this file in Excel, the answer depends on your computer's regional settings. This is dangerous. If you send the same CSV to a colleague in London and a colleague in New York, they might see completely different dates.
The Ambiguity Problem
CSV files lack metadata to define their locale. When Excel imports them, it applies the local system rules. If the file was generated by a US system but opened on a UK computer, dates like 12/01/2026 (Dec 1st) might be read as Jan 12th. Dates that are impossible in the local format (like 31/01/2026 in US mode) are often treated as plain text, creating a mix of real dates and text strings in the same column.
Best Practices
- Use ISO 8601: Whenever generating CSVs, use the
YYYY-MM-DDformat (e.g.,2026-04-03). This is unambiguous worldwide. - Specify Locale on Import: When using Excel's "Get Data" tools, you can specify the origin locale of the file.
Automated Safety
SterileCSV takes a defensive approach. If it encounters ambiguous dates (like 03/04/2026) that aren't in a strict ISO format, it defaults to storing them as Text. This prevents the software from guessing wrong and corrupting your timeline.
It ensures that what you see in the CSV is exactly what you get in Excel, regardless of which country your computer thinks it is in.