Dates change format when combining Excel files: find and fix them

Two different problems that look alike

Excel stores a real date as a serial number (45748 is 1 April 2025) and only formats it as day/month or month/day. A date that has been parsed correctly will survive any combine. The trouble starts with dates that were text when they arrived, usually from CSV exports, and that Excel then interpreted using the regional settings of whichever computer opened them.

Within a single column you now have wrong dates and text, and neither looks broken at a glance. Combined with a UK file that was parsed correctly, the month totals are quietly wrong.

Detect the damage

• =ISNUMBER(A2) returns FALSE for text dates. Filter on FALSE to see them.
• Text dates sit on the left of the cell by default; real dates sit on the right.
• If every day value in one source is 12 or below for the dates that converted, and the text ones all have a first number of 12 or below, that source is month-first.
• Sort the column ascending: text values sort after all real dates and alphabetically, so 01/02/2026 comes before 31/01/2025.

Check each source file separately before combining. Once rows from a UK file and a US file sit in one column, you can no longer tell which convention a valid-looking date followed.

Fix it in Power Query (repeatable)

1. Load each source with Data > Get Data, or the folder of files with From Folder, and open the editor.
2. Make sure the date column is Text first: remove any automatic Changed Type step that touched it.
3. Right-click the column > Change Type > Using Locale, pick Date and the locale the file was written in, for example English (United States) for month/day.
4. For files from a different locale, apply that locale in their own query before appending.
5. Append the queries. Every date is now a real date regardless of your PC's settings.

This is the reliable route when combining files regularly: the locale is recorded in the query, so it does not depend on who refreshes it. The combine Excel sheets guide shows the append step in full.

Fix it with formulas

For a one-off repair, rebuild the date from its parts. If A2 holds 04/13/2026 as text in month/day order:

=DATE(RIGHT(A2,4), LEFT(A2,2), MID(A2,4,2))
=DATE(TEXTAFTER(A2,"/",2), TEXTBEFORE(A2,"/"), TEXTBEFORE(TEXTAFTER(A2,"/"),"/"))

The second version (Microsoft 365) copes with single-digit days and months. Dates that Excel already converted the wrong way round are real numbers, so swap their parts: =DATE(YEAR(A2), DAY(A2), MONTH(A2)). Only apply this to rows from the source you know was misread.

Text to Columns works too: select the column, Data > Text to Columns > Delimited > Next > Next, set Column data format to Date with MDY (or DMY), then Finish.

Prevent it next time

• Ask for exports in ISO format (2026-04-13). It is unambiguous and sorts correctly even as text.
• Import CSVs with Data > From Text/CSV instead of double-clicking them, so you control the type.
• Keep a source-file column when combining, so you can trace a bad date back to its origin.
• After combining, compare monthly row counts with the source files.

When Analistable loads the files you pick, the data stays in your browser and dates are read by DuckDB, which handles ISO dates directly; for ambiguous slash dates, say which order a file uses in your question. For the combine itself, see combining Excel files into one spreadsheet.

A full worked example

Suppose you combine a UK sales export (day/month) with a US one (month/day) and the April totals look too low. Check each source's date column before touching the combined sheet.

Before the fix, a monthly pivot shows £0 for April from this file, £320 in February and £145 in September, and leaves £885 of text dates outside any month. After the fix, all four rows land in April for a total of £1,350. Nothing produced an error at any point, which is why checking each source separately matters.

The order of operations is the key: fix types per source, then combine. Once the US rows sit in the same column as UK rows, 04/02/2026 from either file looks identical and you cannot tell which convention it followed unless you kept a source column.

Times add one more wrinkle. Exports that include a time (2026-04-15 14:30) may convert to a date-time while others hold just a date, so grouping by day needs =INT(A2) or a Date-only type in Power Query to drop the time part. Time zones are not stored in Excel dates at all, so if one system exports in UTC and another in local time, adjust before combining, or late-evening transactions fall on the wrong day.

Frequently asked questions

Why are some dates text and others real dates in the same column?

The file used a different day/month order from your PC. Values that happened to fit your order became (wrong) dates; the rest, like day 13 or later in the month position, stayed text.

Does changing the cell format fix swapped dates?

No. Formatting changes how a stored date is displayed. A date stored as 4 January stays 4 January whatever format you choose.

Why do my combined dates sort in the wrong order?

They are text, so Excel sorts them alphabetically. Convert them to real dates first, then sort.

Can I change Windows regional settings instead?

It works for one file but breaks others and depends on each user's machine. Power Query's Using Locale option is safer because it is stored in the workbook.

Related guides

• How to combine multiple Excel files into one spreadsheet
• Combining data with VSTACK, HSTACK and other dynamic arrays
• How to merge every Excel table into one sheet
• How to pull data from multiple sheets into one summary sheet
• #SPILL! when stacking sheets with VSTACK: causes and fixes