Resolve mixed date text without guessing

Convert declared DMY, MDY and ISO date text with Excel formulas, reject impossible dates, and keep a status for every source row.

An export says 03/04/2026. That can mean March 4 or April 3. A formula cannot discover which meaning the sender intended. This exercise uses a declared format for each row, builds a candidate date, and checks it against the original text before accepting it.

Use this when a small report combines known day-first, month-first and ISO sources. The method retains unresolved and invalid rows so they do not silently disappear from a monthly report. It does not handle timestamps, time zones or unknown date conventions.

Download the twelve cases

Download the date input and the expected results. Keep the input unchanged and work in a new workbook. The input has 12 data rows and three columns: row_id, date_text and declared_format. The expected file contains outputs only; do not use it as the calculation input.

Import date_text as Text before Excel interprets it, then place the three input columns in A1:C13. For a formula-only practice run, format A1:C13 as Text first and copy the literal values from the reference table below, using an empty cell where it says blank. Do not type the word blank. Use General format for D:I so Excel calculates the formulas.

If an import has already changed date_text to a date serial number, return to the original CSV and import again with a Text column. Changing the cell format back to Text does not restore what the file originally said. See the text import procedure for the underlying type-preservation step.

Enter the whitespace case literally

In the reference table, “one leading space then 03/04/2026” describes a value; it is not text to paste into Excel. For record D12, enter one ordinary space followed immediately by 03/04/2026 in the date_text cell, or use the downloaded CSV value. Likewise, “blank” means an empty cell. These distinctions matter because the exercise intentionally rejects edge-spaced text instead of silently trimming it.

State the date contract

DMY means exactly dd/mm/yyyy, MDY means exactly mm/dd/yyyy, and ISO means exactly yyyy-mm-dd. This exercise accepts years 1901 through 9999. The fixed-width rule deliberately rejects 3/4/2026 and surrounding spaces. If your source permits other representations, define and test a separate normalization step rather than relaxing the rule accidentally.

The declared_format value must come from the source specification or its owner. D01 and D02 demonstrate why inspecting the date text alone is insufficient. Unknown formats receive review even when the text looks like a valid date. Empty input receives missing.

Extract the parts and build a candidate

Add these headers in D1:I1: year_part, month_part, day_part, candidate_date, status, iso_date. Enter each formula in its indicated row-2 cell. These formulas use English function names and commas; installations using a semicolon list separator need semicolons between arguments. Formula blocks scroll horizontally on narrow screens; copy each complete formula, including its leading equals sign.

D2 extracts the year

=IFERROR(IF(B2="","",IF(C2="ISO",VALUE(LEFT(B2,4)),IF(OR(C2="DMY",C2="MDY"),VALUE(RIGHT(B2,4)),""))),"")

E2 extracts the month

=IFERROR(IF(B2="","",IF(C2="ISO",VALUE(MID(B2,6,2)),IF(C2="DMY",VALUE(MID(B2,4,2)),IF(C2="MDY",VALUE(LEFT(B2,2)),"")))),"")

F2 extracts the day

=IFERROR(IF(B2="","",IF(C2="ISO",VALUE(RIGHT(B2,2)),IF(C2="DMY",VALUE(LEFT(B2,2)),IF(C2="MDY",VALUE(MID(B2,4,2)),"")))),"")

G2 builds an unapproved candidate

=IF(COUNT(D2:F2)<>3,"",IFERROR(DATE(D2,E2,F2),""))

Format G2:G13 as yyyy-mm-dd. G is a diagnostic column, not the accepted output. DATE can roll an overflowing day into the next month: our D05 produces March 3 from the impossible February 31. A plausible-looking date in G is therefore not enough.

Validate before releasing a result

In H2, reconstruct the source's exact representation and compare it with the untouched text. This catches overflow, wrong separators, shortened dates and extra spaces. The year guard excludes dates before the stated range.

=IF(B2="","missing",IF(AND(C2<>"DMY",C2<>"MDY",C2<>"ISO"),"review",IF(G2="","invalid",IF(AND(D2>=1901,EXACT(B2,TEXT(G2,IF(C2="DMY","dd/mm/yyyy",IF(C2="MDY","mm/dd/yyyy","yyyy-mm-dd"))))),"accepted","invalid"))))

In I2, release an ISO text value only for accepted rows:

=IF(H2="accepted",TEXT(G2,"yyyy-mm-dd"),"")

Select D2:I13 and use Fill Down so row 2's formulas populate rows 3–13. Check a middle row and the final row: their references should use that row's B and C cells. I contains export-ready text, while G contains Excel date values where a candidate could be calculated. Use G for date arithmetic only on accepted rows; never export all candidate dates indiscriminately.

Compare every outcome

You should have four accepted, six invalid, one review and one missing row: 12 accounted-for records. Check the categories with COUNTIF(H2:H13,"accepted"), changing the quoted status for each count. A blank ISO output alone cannot tell you why conversion was withheld; keep status alongside it.

Try one reversible edit: change B9 from 29/02/2025 to 28/02/2025. H9 should become accepted and I9 should become 2025-02-28. Restore the original and confirm invalid returns. If the result does not change, check that the formula cells are General, contain formulas rather than pasted text, and are recalculating.

Decide what happens to exceptions

Ask the source owner to resolve unknown formats. For invalid rows, compare the untouched input with the source export and correct only when you have supporting evidence. Missing dates need an explicit business rule; do not replace them with today's date or zero. Keep a record of corrections and retain row_id so comparison against another list can account for every record.

After fixing a source, rerun the checks and keep the remaining exceptions visible. If you send a CSV, follow the export check and verify the ISO text and statuses after reopening the delivered file.

Actual test record

On October 6, 2026, the 12 literal inputs were written into a separate connected Excel for Mac 16.113.2 workbook, with input cells formatted as Text. Excel calculated the formulas above, and the returned values and formulas were recorded. All 12 status/output pairs matched the expected file. D05's candidate was 2026-03-03, but its status remained invalid. The B9 correction and restoration also produced the expected changes.

This is a native worksheet-formula test, not a new CSV-import or Power Query test. It used a Chinese-language Excel interface with English formulas and data. No English-interface or Windows application test is claimed. Independent file checks validate the CSV records and expected calendar dates. Download the dated test notes for the scope and results.

References

Microsoft documents how DATE handles components, how TEXT produces formatted text, and how EXACT compares strings. The fixed-width acceptance contract and twelve cases above belong to this exercise.