Standardize Mixed Date Formats

What it does

You have a column of dates that look like dates but Excel insists are text. Maybe they came from a CSV. Maybe a vendor system writes them as 28/04/2026 and your Excel locale wants 04/28/2026. Either way, SUMIFS by month doesn't work and chart axes display them as raw strings. The macro asks you which format the source is in (US M/D/Y, EU/UK D/M/Y, or ISO Y/M/D), then parses each cell, converts it to a real Excel date value, and applies a YYYY-MM-DD display format so the result is consistent and unambiguous.

Real-world example

Picture a junior FP&A analyst at a UK-based subsidiary of an American parent company. Every Monday morning the parent company sends a CSV of yesterday's transactions for him to roll into the regional model. The parent's system writes dates as 4/28/2026. His Excel locale is set to UK (D/M/Y), so when he opens the CSV, Excel does its best: rows where the day is over 12 (like 4/28/2026) get treated as text because UK locale can't parse them as dates. Rows where the day is 1 to 12 get silently misinterpreted (so 4/8/2026 gets read as 4 August, when the source meant 8 April).

His current workaround: open the CSV in a text editor first, run a regex find-replace to swap the day and month positions, save, then re-open in Excel. Or, more often, just give up and re-key the dates by hand for the rows that matter most. Both options are awful and one of them silently corrupts the data.

With this macro he selects the date column, runs the tool, picks 1 for US M/D/Y, and 1,400 rows go from text-strings-and-misread-dates to real Excel dates in YYYY-MM-DD display format in about 2 seconds. The popup tells him how many got converted and how many were skipped (the few cells with notes like "see footnote" that he investigates separately). His SUMIFS by month start working immediately.

On a 1,400-row weekly transactions file, expect about 6 minutes saved per import. Across a year that's roughly 5 hours back, plus the much bigger win of not silently swapping months and days on half his rows.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the column or range of date text to convert.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick StandardizeDates, click Run.
3. Type 1 for US format (M/D/Y), 2 for UK or EU format (D/M/Y), or 3 for ISO format (Y/M/D).
4. The macro parses each cell using your chosen format and writes back a real Excel date with YYYY-MM-DD display. The popup reports converted versus skipped counts.

FAQ

What separators does it understand?

Slashes /, dashes -, and dots .. So 04/28/2026, 04-28-2026, and 04.28.2026 are all parsed identically. The first separator found in the cell wins. Mixed separators within a single cell are treated as the first one found, which can cause parse errors on weird inputs like 04/28-2026.

What about 2-digit years?

Years from 0 to 99 are auto-prefixed with 2000. So 04/28/26 becomes 2026-04-28. This is the standard Excel default and matches almost every modern use case. If you have actual 1990s dates stored as 2-digit years, fix them in the source before running this macro, or edit the .bas source to change the rollover year.

What if my column has dates in mixed formats?

The macro processes every cell using the single format you choose. So if half your column is M/D/Y and half is D/M/Y, you'll need to run the macro twice on the right subsets. There's no reliable way to auto-detect format per cell, because for many dates either interpretation is technically valid (04/05/2026 is both April 5 in US and 4 May in UK).

Does it skip cells that are already real Excel dates?

Yes. The macro only processes cells whose VarType is a string. Cells already stored as Excel date serial numbers get skipped. The popup shows the skipped count so you can confirm the picture matches your expectation.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. Always work on a copy of the file if it's the only copy. The macro runs entirely offline. No signup, no account, no data leaves your machine.