Excel dates stored as text: get an AI formula and test it

Thirty order dates exported from another system arrive in these four formats, all stored as text. Sorting goes by characters, and date-range totals skip them. The sample data is made up.

[1] Confirm they are text. Put =COUNT(A2:A31) in an empty cell; 0 means none of the 30 values is a number (date).

[2] Write a prompt with format examples as input/output pairs. OpenAI's prompt engineering guide explains that showing examples of input and desired output lets the model follow the pattern. Whether a slash means month/day or day/month can't be told from the data, so pin it down with the examples and a sentence.

Cells A2:A31 of an Excel sheet hold order dates exported from another system, stored as text. Four formats are mixed.The slash format is US-style month/day/year. Format examples (raw -> date I want):2026.03.05 -> March 5, 202620260325 -> March 25, 20267/8/2026 -> July 8, 20262026-01-12 -> January 12, 2026 <data>{the 30 values from column A, one per line}</data> Give me one formula to put in B2 and fill down to B31.- The result must be a real Excel date (serial number), not text.- Do not use functions that read a date string according to the PC's regional settings (DATEVALUE, or VALUE on a date string). Cut out year, month and day yourself and build the date with DATE.- If a value matches none of the four formats, return "CHECK" instead of a blank.- Use only functions available in Excel 2016 (no LET, no TEXTSPLIT).- Give the formula first in a code block, then explain in three lines or fewer which part handles which format.

[3] Gemini's formula. If there is a slash, FIND locates it and the parts are cut out; the dot/dash formats and the 8-digit format are cut with LEFT, MID and RIGHT.

=IFERROR(IF(ISNUMBER(FIND("/",A2)),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1, … ),IF(OR(ISNUMBER(FIND(".",A2)),ISNUMBER(FIND("-",A2))),DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)),IF(AND(LEN(A2)=8,ISNUMBER(--A2)),DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),"CHECK"))),"CHECK")

[4] Test the formula without Excel. The Python package formulas evaluates Excel formulas; eval_formula.py runs it for all 30 rows, compares with the expected dates, and then tries four impossible dates. The package leaves --"03" as text instead of turning it into 3, so the test copy alone has --X rewritten as VALUE(X).

$ python3 eval_formula.py e1.txt dates.csv 13/5/2026 20261305 2/30/2026 2026.02.2930 rows, 0 differextra 13/5/2026    -> 2027-01-05extra 20261305     -> 2027-01-05extra 2/30/2026    -> 2026-03-02extra 2026.02.29   -> 2026-03-01

All 30 rows are right. But month 13 and February 30 become plausible dates instead of "CHECK". Microsoft's DATE help says a month over 12 adds months into the next year and a day past the end of the month carries into the next month. If a few rows were exported day/month, they turn into wrong dates without any error.

[5] Paste the test result back and ask for a fix. The follow-up said that 13/5/2026 came out as 2027-01-05 and asked for a date only when the month is 1–12 and the day exists. Gemini added a check to all three branches: the MONTH of the built date must equal the month cut from the text. The formula doubles in length, so it isn't shown here. Run the same test:

$ python3 eval_formula.py e2.txt dates.csv 13/5/2026 20261305 2/30/2026 2026.02.2930 rows, 0 differextra 13/5/2026    -> CHECKextra 20261305     -> CHECKextra 2/30/2026    -> CHECKextra 2026.02.29   -> CHECK

[6] Finish in Excel. Fill =ISNUMBER(B2) down column C and check that =COUNTIF(C2:C31,FALSE) is 0. Put =TEXT(B2,"yyyy-mm-dd") in column D and compare 10 rows with column A, starting with rows like 7/8/2026 where both month and day are 12 or less. For each "CHECK" row, look up the original value in the source system and fix it by hand.