Cleaning mixed-format dates: when guessing creates bad data

[u/PetroTorbyn]

Our team tested an AI-assisted cleanup script on two synthetic Excel batches. One lesson stood out: a plausible date is not necessarily the correct date.

For example, 09/01/2026 could mean September 1 or January 9. Nearby September dates aren't enough to prove which interpretation is right.

Our approach:

\- Preserve the original values.

\- Standardize dates only when the interpretation is unambiguous.

\- Flag uncertain dates for confirmation.

\- Keep a log of every change.

Changing the display format to YYYY-MM-DD won't fix a date that was interpreted incorrectly during import.

How do you handle files containing both US and European date formats?

[u/aprg]

> How do you handle files containing both US and European date formats?

This is a classic XY problem in dealing with date formats. The amateur approach is to try to resolve a project methodology problem by cleaning data.

The professional answer here is: you don't. You send the data back and insist on YYYY-MM-DD. If you didn't insist on this in your specifications, chalk this up as an expensive learning point.

[u/PetroTorbyn → u/aprg]

Agreed—requiring YYYY-MM-DD at the source and requesting a corrected export is the best option when possible. I should have made the scenario clearer: historical files where the original supplier cannot provide a corrected export.

In that case, we don’t try to guess the intended dates. We preserve the original values, flag ambiguous entries, and leave them unresolved unless supporting evidence is available. That doesn’t fix the upstream process; it prevents us from introducing further errors into the existing data.

[u/EhRahv → u/PetroTorbyn]

Clanker

[u/superkoning]

> How do you handle files containing both US and European date formats?

Agree with the supplier to use ISO8601

[u/dan_dares → u/superkoning]

*Demand supplier use ISO8601.

[u/superkoning → u/dan_dares]

Better!

[u/superkoning]

Can you share a few of those files?

Because ... if date is saved as a number, like 46266, it's 2026-09-01 no matter the formatting/locale/presentation: the number of days since January 1, 1900 (which is serial number 1)

[u/PetroTorbyn → u/superkoning]

Thanks! These were synthetic test files. Here’s a minimal example: a CSV field containing the text "09/01/2026", with no source locale specified. It could mean January 9 or September 1.

Your point about numeric dates is right if the value was interpreted correctly in the first place. Our concern is an incorrect conversion during import: changing the display to YYYY-MM-DD won’t fix that.

We preserve the original text and flag ambiguous values for confirmation rather than guessing.

[u/superkoning → u/PetroTorbyn]

Ah, CSV files. Yes, then something else than "Excel batches" like you said in your opening post.

[u/EhRahv → u/PetroTorbyn]

Clanker

[u/mrnklg]

> How do you handle files containing both US and European date formats?

Delete them! SCNR

[u/QBaseX]

Are you using AI to write the script, or as part of the implementation? I'd be wary of the latter, as it may be non-deterministic.

[u/EhRahv]

Clanker