Nothing breaks a Monday import like 03/04/2025 meaning March 4 in one file and April 3 in another. Date formatting is data hygiene, not cosmetics. Clean exports save hours of VLOOKUP surgery.
Store ISO, display local
Keep YYYY-MM-DD in CSV exports and database extracts. Let spreadsheets format for regional readers in a view column. Sorting stays correct; pivots group by month without text tricks.
If leadership wants DD/MM/YYYY on PDFs, generate that from the ISO column—never type it back into the source.
Separate date, time, and zone
Event logs need three fields: instant in UTC (or offset), local date for reporting, and time zone name. Merging zone into a pretty string ("3 May 2025 9:00 AM PT") without the offset creates audit pain when daylight saving shifts.
Text that looks like dates
Excel converts 2025-05-01 correctly but may turn 2025.05.01 into text. CSV from European systems sometimes uses dots. Run a formatter pass to validate and normalize before BI tools ingest.
A Date Formatter helps prototype target formats on sample rows before you rewrite ten years of exports.
End-exclusive ranges
Subscriptions and hotel stays often use end-exclusive dates (checkout day not charged). Document whether end_date is inclusive in your schema README. Mixed inclusive/exclusive endpoints double-count or skip days in duration math.
Null and sentinel policy
Do not use 00/00/0000 or N/A in date columns. Use empty cells and a boolean is_missing flag so averages do not break silently.
Multi-country teams in one sheet
When London and Chicago edit one Google Sheet, enforce ISO in the raw tab and add a "display" tab per locale. Never let Chicago type 4/5/2025 into the raw tab because London already entered May 4.
Pivot tables and QUERY functions read the raw tab only. Training is one sentence: "Raw tab is ISO; play in Display."
APIs and CSV round trips
APIs should emit RFC 3339 timestamps with offset. Consumers parse once; display layers handle locale. If you only send "05/03/2025," every client guesses. Document the parsing rule in OpenAPI examples, not only prose.
When Excel reopens a CSV and mangled dates, import via Power Query with locale disabled and column typed as date from ISO text. Teach finance one screenshot workflow instead of fifty manual fixes.
Audit trails and filenames
Filename dates in ISO sort chronologically: export_2026-05-12.csv beats export_5-12-26.csv. Batch jobs that glob files depend on this more than humans do.
Keep a changelog row when you migrate formats: "from MM/DD/YYYY text to ISO on 2026-05-01." Analysts joining mid-year will not blame May variance on May math.
Legacy migrations
Mainframe extracts often use YYMMDD integers. Map through a staging column that parses explicitly: SUBSTR and DATEVALUE are not interchangeable across locales. Keep the integer column read-only after conversion so nobody re-exports the dirty legacy.
Spot-check century: two-digit years belong in museum exhibits, not new pipelines. If you inherit them, expand to four digits in the first transform step with a documented pivot year rule.
Add a data-quality dashboard tile for "non-ISO dates detected this week" so fixes happen continuously, not at year-end close.
Biweekly and semimonthly payroll
Pay periods labeled "1st–15th" still store as ISO start/end in payroll tables. Semimonthly versus biweekly confusion is a separate problem from display format, but dirty text dates make it worse when HR joins tables on strings.
Time-clock punches should store UTC instant plus worksite zone name; local wall time alone cannot reconstruct compliance reports across DST.
Machine learning features
Feature stores should ingest ISO dates and derive day-of-week in the pipeline, not in scattered notebook cells. Training-serving skew often starts when one engineer parsed dates with locale guesswork six months ago.
Document timezone used for "event date" features in the model card so production monitoring can spot DST regressions before scores drift.
Archival and compliance
Regulators ask for retention in unambiguous instants. Archive ISO with offset even if daily reports show local dates only. Retrieval in ten years should not require guessing which locale exported the CSV.
Legal hold exports should include a README.txt describing date columns in one paragraph—future counsel will thank you. Treat that README as part of the deliverable, not optional commentary.
Frequently Asked Questions
Why not use locale defaults in CSV?
CSV has no locale. Readers guess. ISO is the lingua franca.
How do I fix already messy files?
Parse with explicit format strings, output ISO, spot-check rows where day > 12 to catch swaps.
Should I include time in date columns?
Split them. Midnight assumptions ruin global teams.
What about Excel serial numbers?
Export as ISO text for partners; keep serials only inside Excel-native workflows.
How do I document formats for API consumers?
Publish the ISO pattern, inclusivity rules, and zone field names in one page—examples beat prose.
After normalization, duration between rows belongs in a Date Calculator, not in format strings—keep formatting and calculation responsibilities separate.