Dates are the worst offenders in messy data. "3/4/2026", "March 4, 2026", "2026-03-04", and "4 Mar 26" can all sit in the same column. Some Sheets reads as real dates, some it treats as plain text, and you cannot sort or filter across the mix.
The goal is one format for the whole column. Getting there is the hard part.
The Old Way (DATEVALUE and Guesswork)
The standard route is DATEVALUE to parse the text into a date number, then TEXT to reformat it.
=TEXT(DATEVALUE(A2), "yyyy-mm-dd")It works until it does not. DATEVALUE fails on formats it does not recognize, so "4 Mar 26" throws a #VALUE! error. It also guesses wrong on ambiguous dates. Is "3/4/2026" the third of April or the fourth of March? DATEVALUE picks one based on your locale and never tells you.
So you wrap it in IFERROR, then add nested checks for each format you spot, and the formula grows into a small program that still misses the next weird row.
The FITS Way (Just Ask)
With FITS, you name the format you want and let the AI read each date the way a person would.
=FITS("Convert this date to YYYY-MM-DD format: " & A2)Want it written out for a report instead? Just say so.
=FITS("Rewrite this date as Month DD, YYYY: " & A2)It handles spelled-out months, two-digit years, and mixed separators in the same pass, because it reads the meaning rather than matching a rigid pattern.
When to Use Each
If every cell is already a real date value, a Format menu change is all you need. Reach for =FITS() when the column mixes text and dates, when formats vary row to row, or when this is one step in a bigger cleanup. Dates rarely arrive clean, so pair it with trimming extra spaces, formatting phone numbers, or a full data normalize pass. Cleanup is one job on a long list. Read the full guide to automating Google Sheets tasks you used to need regex for.
Stop Fighting DATEVALUE Errors
FITS puts plain-English AI formulas inside Google Sheets. Name the date format you want. Get a clean column back. Free tier included.