Practical diagnosis

Import date columns without losing the evidence

Reviewed 8 October 2026 · Synthetic examples

A CSV carries text fields, not a workbook’s date-system setting or cell formatting. A cleanly separated file can still produce wrong dates. This workflow checks date interpretation; it does not repair delimiters, quoting, encoding, or malformed CSV structure.

Keep a raw copy and a small test Start with a separate, synthetic three-row file containing a label and a raw date field: alpha,2026-04-03; beta,03/04/2026; gamma,2026-02-29. Use one record per line with a comma between the two fields. The last date is deliberately impossible because 2026 is not a leap year. The middle date is deliberately ambiguous.

Write an acceptance rule before importing: preserve all three strings initially; convert alpha only under a stated YYYY-MM-DD contract; hold beta until its order is known; reject gamma as a calendar date. Expected decisions are therefore “convert,” “needs convention,” and “invalid.” A workflow that silently turns all three into ordinary dates has failed this test.

Excel: inspect the conversion steps Microsoft documents that opening a CSV uses current default data-format settings. Prefer a controlled import when interpretation matters. In supported desktop Excel versions, Data > From Text/CSV provides a route into the import process. In Power Query inspect automatic Changed Type steps and retain the date column as Text before deliberate conversion. Converting an already misread date back to text does not recover the original field.

Sheets: verify preservation, do not assume it Google documents File > Import and options to insert a new sheet or create a spreadsheet. Use a disposable destination, avoiding replace options while testing. Inspect the raw imported cells against the original three strings and verify that they remain text, not numeric dates with matching displays. With the first raw date in B1, =ISTEXT(B1) should return TRUE; repeat for B2 and B3. A numeric date returns FALSE. Do not rely on appearance alone. If the import interface offers a conversion control, disable automatic conversion for the preservation pass; this guide does not assert that the same control exists on every client. If exact text is not preserved, stop and choose a text-preserving workflow before using real records.

Construction is not validation Both providers document DATE rollover behavior. =DATE(2026,2,29) produces March 1, 2026 rather than rejecting the invalid February date. When parsing components, compare the resulting year, month, and day back to the input, and reject any mismatch. Do not treat a formula returning a date as proof that the input was valid.

After conversion, retain raw text beside parsed dates and review all failures. If you export again, inspect the output as text and re-import the synthetic sample into the intended destination. Record the app, locale, date system, interpretation rule, and observed outcomes. A passed small test supports that exact route, not universal compatibility.