Messy Date Cleaner

Sort a date column and watch it scramble: half the cells are real dates, half are text pretending to be dates, and the rest are timestamps with no year. This free messy date cleaner takes that pasted column and turns it into clean, sortable dates in seconds — no sign-up, no upload. Give it the ugliest column you’ve got.

InputClean dateStartEndSort keyStatus
Paste a date column above - results appear here as you type.

What this messy date cleaner fixes

It reads each line of your pasted column and figures out what date you meant. Eight-digit blobs like 03041956, six- and seven-digit variants, slashed dates like 3/4/1956, ISO timestamps, written-out month names and ranges such as 10/7 to 10/10 — all recognised.

Every date gets validated with a round-trip check, so impossible dates like February 30 are rejected instead of quietly becoming March 2. Each row earns a colour-coded tag — OK, Fixed, Ambiguous or Invalid — plus a YYYYMMDD sort key you can sort on in Sheets or Excel.

You’re in control of the interpretation: day/month order (MDY, DMY or YMD), the default year for entries like 10/7, the two-digit-year pivot, DOB mode that rejects future dates, and the output format — YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY or your own custom pattern.

How to clean a date column

Here’s the quickest way to run a messy date cleaner pass on a fresh column.

  1. Copy the messy column from Google Sheets and paste it into the box — one date per line.
  2. Pick your day/month order. If your data is day-first (30/4/1956), choose DMY — the tool warns you when the batch looks day-first.
  3. Set the default year for entries like 10/7, and the two-digit-year pivot if you’ve got years like 56.
  4. Choose single date, date range or timestamp-to-date-only mode, then pick an output format.
  5. Results appear as you type. Copy as TSV, download the CSV, or copy just the problem rows to fix by hand.

Clean a whole column of dates, mixed formats included

Paste the whole column and the cleaner works through every row, turning written months, 8-digit blobs and mixed MDY and DMY dates into one clean format. Each row gets a status tag, so you can see which dates were fixed and which need a look.

Worked example: a mixed-format column, cleaned

Click Load example (or paste these lines): 03041956, 3/4/1956, 2026-08-05 13:49:12, 10/7 to 10/10, February 3, 2025, 30/02/2024.

03041956 becomes 1956-03-04 with sort key 19560304, tagged Ambiguous — it could be March 4 or April 3, so the result follows your MDY/DMY setting while the tag reminds you to double-check. 3/4/1956 lands on the same result for the same reason.

2026-08-05 13:49:12 is trimmed to 2026-08-05 and tagged Fixed. The range 10/7 to 10/10 picks up the default year and becomes 2026-10-07 to 2026-10-10, tagged Ambiguous — 10/7 could equally be July 10, so the tag prompts a double-check.

February 3, 2025 is already unambiguous, so it’s tagged OK with sort key 20250203. 30/02/2024 is Invalid — February 30 doesn’t exist, and the round-trip check catches it.

Status tags, explained

OK means the date was already clean and unambiguous. Fixed means the tool repaired something — dropped a time, pivoted a two-digit year, filled in a default year, or split a range. These four tags are how the messy date cleaner reports its confidence in each row.

Ambiguous means the date could be read two ways (3/4/1956 is March 4 in MDY, April 3 in DMY). The tool follows your day/month order, but flags the row so you can double-check the risky ones instead of trusting them blindly.

Invalid means the line couldn’t be turned into a real date — a typo, an impossible day, or a future date in DOB mode. Copy problem rows only grabs the Ambiguous and Invalid rows so you can review them separately.

More cleanup tools

Cleaning dates is often one step in a bigger tidy-up. If your column mixes numbers and text, the number-and-unit splitter separates them. If you paste formulas that keep shifting, a tool that pins shifting references in place holds them steady. And if your dates include week numbers, the Bulk MMWR Epiweek Converter translates them.

FAQs

What if some dates are MDY and some are DMY in the same column?

Go with the order most rows follow, then let the status tags do the triage. Rows with a first part above 12 (like 30/4/1956) can’t be MDY, so the tool warns that the batch looks day-first and suggests DMY. Rows that stay ambiguous under your chosen order get tagged Ambiguous — copy problem rows only and eyeball just those instead of re-checking the whole column.

How are date ranges handled?

Ranges like 10/7 to 10/10 or 12/28 - 1/3 are split into a start and an end. A year written on only one end is carried across to the other, and if the end lands earlier than the start the year rolls over — 12/28 - 1/3 becomes December 28 to January 3 of the following year. The table shows the start, the end and a sort key based on the start date.

Does anything get uploaded?

No. Every calculation runs in JavaScript in your own browser tab; your pasted dates never leave your device and nothing is sent to any server. Clearing the box wipes the results instantly.

Can I clean dates in Excel without writing a formula?

Yes. Paste the column here and the cleaner returns the dates in one format, with a YYYYMMDD sort key you can paste back and sort on in Sheets or Excel. There is no formula to write, and nothing is uploaded.

What happens when MDY and DMY dates are mixed in one column?

The cleaner reads each row on its own, so a US-style date and a UK-style date in the same column are both handled. Rows it cannot read get a status tag instead of being silently dropped, so you can fix them by hand.

Note: all processing happens in your browser — nothing you paste into this messy date cleaner is uploaded anywhere.

Related tools: before you share a cleaned column, anonymize names in a column before you share it.

Leave a Comment