How to Standardize Mixed Date Formats in Excel

The "my dates aren't really dates" problem

You imported a CSV. The date column shows 04/28/2026. Looks like a date. But SUMIFS by month returns 0, the chart axis displays the entries as raw text, and =A2 < TODAY() returns #VALUE!. The reason: those "dates" are stored as text, possibly in a format your Excel locale doesn't recognize.

This is one of the most common Excel data bugs. Here are three ways to fix it. The right one depends on whether the format is consistent and what locale your Excel is set to.

TL;DR — Key takeaways

• Excel stores dates as serial numbers internally. Anything else (text strings) won't work in date math, charts, or filtering by month.
• =DATEVALUE() parses text to a date in your locale. It fails if the text format doesn't match your locale's expectation (US Excel can't parse 28/04/2026).
• Power Query has a Locale-aware change type that lets you tell it which locale to interpret the text as.
• A free VBA macro lets you pick the source format (US, UK/EU, ISO) explicitly, regardless of your Excel locale, and writes back real dates with a consistent display format.

Why dates get stuck as text

Common causes:

• CSV imports where the source system wrote dates in a different locale than your Excel is set to.
• Excel's "import as text" option during the CSV import wizard sometimes assumes text for date columns.
• Web-paste from sources that wrap dates in HTML or include extra characters.
• Cross-locale exports (US system, UK Excel, or vice versa).

The symptom: cells look like dates but Excel treats them as text strings. Filter by Date doesn't work. Sort by date doesn't work. Date math doesn't work.

Method 1: =DATEVALUE() formula

Excel's built-in DATEVALUE() function parses a text date and returns the underlying date serial number.

=DATEVALUE(A2)

Wrap the result with a date format cell to display it as a date:

=DATEVALUE(A2)

Then format the column as yyyy-mm-dd or whatever you prefer.

Workflow:

1. Insert a helper column.
2. Drop =DATEVALUE(A2) in the first row.
3. Fill down.
4. Format the helper column as a date.
5. Copy, Paste Special > Values back over the original.
6. Delete the helper.

The catch: DATEVALUE parses based on your Excel's locale. US Excel parses 04/28/2026 correctly but errors on 28/04/2026. UK Excel does the opposite. There's no way to tell DATEVALUE which locale to use, so it fails on cross-locale data.

Method 2: Power Query with explicit locale

Power Query lets you tell it explicitly which locale to interpret the source text as.

1. Load the data into Power Query.
2. Right-click the date column header > Change Type > Using Locale.
3. Pick the source locale (e.g., English (United States) for M/D/Y or English (United Kingdom) for D/M/Y).
4. Pick the target type (Date).
5. Close & Load.

Power Query parses every cell using the locale you specified, regardless of your Excel's display locale.

The catch: Power Query is overkill for a one-off and the "Change Type Using Locale" feature is buried in a submenu most users haven't encountered. For a recurring weekly import, this is the right answer.

Method 3: The free VBA macro (locale-explicit, one-shot)

Download Standardize Mixed Date Formats. Free .xlsm with one macro.

Steps:

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

The result is real Excel dates. SUMIFS by month works. Charts treat them as dates. Date math works. The display format is consistent regardless of your Excel locale.

A common scenario: cross-locale CSV import

US parent company sends UK subsidiary a daily transactions CSV. Dates come in as 4/28/2026. UK Excel is set to D/M/Y locale.

When opened, rows where the day is over 12 (4/28/2026) get treated as text because UK locale can't parse them. Rows where the day is 1 to 12 get silently misinterpreted (so 4/8/2026 is read as 4 August when the source meant 8 April).

Macro fix:

1. Select the date column.
2. Run Standardize Mixed Date Formats.
3. Pick 1 for US (M/D/Y) format because that's how the source wrote them.
4. Macro parses every cell as M/D/Y and writes back proper dates.

The cross-locale interpretation problem is solved because the macro doesn't trust Excel's locale; it uses the format you explicitly specified.

Comparison: which method to use

Method	Cross-locale support	Result type	Setup time
=DATEVALUE()	No	Real date (with helper)	2 min
Power Query (Locale-aware)	Yes	Real date (refreshable)	5 min
VBA macro	Yes (you pick the format)	Real date (one-shot)	5 sec (after install)

Edge case: 2-digit years

04/28/26 could be 2026 or 1926. The macro auto-prefixes years from 0 to 99 with 2000, so 26 becomes 2026. This is the standard Excel default and matches almost every modern use case. If you have actual 1990s dates as 2-digit years, fix them in the source first.

Edge case: mixed formats in one column

If half your column is M/D/Y and half is D/M/Y, no automatic tool can correctly distinguish them. For dates where the day is 1 to 12, both interpretations are valid. The macro processes everything with one format, so you'd need to:

1. Identify which rows are which format (often impossible to tell without source context).
2. Sort or filter to separate them.
3. Run the macro once per format on the appropriate subset.

The honest answer is: fix mixed formats at the source if at all possible.

Frequently asked questions

What separators does the macro understand?

Slashes /, dashes -, and dots .. So 04/28/2026, 04-28-2026, and 04.28.2026 all parse identically. The first separator the macro finds in the cell wins.

Can it handle dates with month names like "April 28, 2026"?

No. The macro expects all-numeric dates with a separator. For text-month dates, use =DATEVALUE("April 28, 2026") which parses month names in your locale.

Will it skip cells already stored as Excel dates?

Yes. The macro only touches cells whose value is a string. Cells already stored as date serial numbers are skipped. The popup shows the skipped count so you can verify.

What about times?

The macro parses dates only, not date-times. If your cells include a time component (04/28/2026 14:30), the parse will likely fail. Strip the time first or use a date-time-aware tool.

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.

What to do next

If your CSV imports also have numbers stored as text (a sibling problem), run Convert Text to Numbers on the numeric columns. If whitespace is contaminating cells (a common source of DATEVALUE failures), run Trim Whitespace From All Cells first.