Keep the original values beside the result
Work on an authorized copy. Retain the original date field and add separate fields for the interpreted date, source reference and review status. If a previous import has already changed the underlying value, return to the original export when available. Formatting the changed value will not reveal the original intent.
Separate sources before applying a rule
A merged workbook may contain exports from different regions or systems. Group records by their known source and check the source's date specification. A value with a day above twelve can provide a clue, but it does not prove every row uses the same convention. Ask for confirmation where the source documentation is missing.
Distinguish interpretation from presentation
Interpreting text establishes a date value. Formatting controls how a known value is displayed. In Power Query, locale affects the conversion from text to a date; Microsoft describes using a specified locale when changing a column's type. Apply that only after the source convention is established.
Source: Microsoft Learn — Data types in Power Query: locale and conversion.
Flag the cases that need a human answer
Some values lack a year, mix a date with a note, or record only a month. Preserve that uncertainty. Do not replace an unknown document date with the file's modified time. Likewise, a timestamp and a date-only field are different: identify whether a time zone matters before reducing one to the other.
- Ambiguous day and month: confirm the convention.
- Missing year: retain the source text and ask if the year is required.
- Invalid date: keep the error visible rather than substituting today.
- Month-only record: agree whether a partial date is acceptable.
- Mixed source column: split the review by source before transformation.
Check the transformed results at boundaries
Compare samples from each source, including month ends, year changes and ambiguous-looking values. Check row counts and count unresolved entries. Sort the interpreted dates to spot unexpected extremes, but do not automatically replace an outlier; an old document may have a perfectly valid old date. Describe the review coverage in the handoff.
State the rule so the next import stays consistent
Deliver a date convention with the cleaned file and keep unresolved text available. A consistent representation such as year-month-day can make the result easier to read, but it should only contain dates whose meaning was established. For recurring exports, agree the source setting and check a sample before combining the next batch.
Original: 03/04/2026 Source: supplier export A Source convention: unconfirmed Interpreted date: unresolved Action: confirm day/month order with source owner
A few useful answers
Can I fix mixed dates by formatting the entire column?
Formatting changes presentation, not missing knowledge about the source. Establish which values are real dates, which are text and which remain ambiguous first.
Should unknown dates become the first day of the month?
Only if the receiving workflow explicitly defines and labels that convention. Otherwise the change invents precision that the source did not provide.
What should I keep after cleanup?
Keep the original source, the agreed transformation rule, the reviewed output and the unresolved-value list under the project's handling agreement.