How to fix dates stored as text in Excel before client delivery

You finish the monthly trend chart, glance at it before sending, and the line zig-zags like a heartbeat. January sits after October. The 2026 total is missing entirely. You didn't build the chart wrong — the Order Date column is text, not dates, so Power BI sorted it alphabetically and quietly dropped the rows it couldn't parse. If you'd sent it ten minutes earlier, the client's first impression of the quarter would have been a chart that says sales collapsed in a month they actually grew.

Why a text date looks fine and behaves badly

A date in Excel is really a number — 1 January 2026 is the serial 46023 — and the slash-and-digits you see is just formatting on top. When a column comes in as text, the cells look identical to real dates but carry no underlying number, so anything that sorts, filters, or groups by time treats "12/01/2026" as the string "12/01/2026" and files it next to "12" and "120". The giveaway takes two seconds: real dates right-align by default, text dates left-align. Select the column and glance at the alignment before you trust it.

CSV files are the worst offenders. Open one in Excel and any column the parser isn't certain about arrives as text — sometimes with an invisible leading apostrophe that pins it that way no matter how many times you reformat the cell. That's why the fix is never "change the cell format to Date": formatting only changes how a value looks, not what it is. You have to convert the underlying value, then apply the format.

The example: one column, five disguises

Here is a real Order Date column, five rows, five ways of storing the same kind of value:

Order Date
03/04/2026
2026-04-03
4-Mar-26
March 4, 2026
44621

Every one of these is text in this file, and each needs a different fix. 2026-04-03 and March 4, 2026 convert cleanly with =DATEVALUE(A2). The 44621 is an Excel serial that got pasted as text, so it needs =VALUE(A2) instead. And 03/04/2026 is the dangerous one: is it 3 April or 4 March? DATEVALUE will guess based on the machine's locale, which means the same file converts differently on your laptop and the client's. When you know the source format, force it explicitly rather than letting Excel guess:

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

That reads day-month-year positions out of the string and builds a real date with no ambiguity. After any of these, format the result column as a date, then copy and paste-special as values so the numbers stick.

The fix, step by step

1. Confirm the problem. Select the column; if the values left-align, they're text. Confirm with =ISTEXT(A2) returning TRUE.
2. Identify the source format before converting — DMY, MDY, or serial. Ask the client if the day-month order isn't obvious; never assume.
3. Convert with the right tool — DATEVALUE for written dates, VALUE for serials, an explicit DATE() for ambiguous slashed formats.
4. Or use Text to Columns for a whole column: Data → Text to Columns → Delimited → Finish, and on the last step set Column data format to Date with the correct DMY or MDY order.
5. Standardize the display to yyyy-mm-dd so the column sorts correctly and reads the same in every locale.
6. Verify the range. Sort ascending and check the earliest and latest dates fall inside the reporting period — a stray 1900 or 2099 means a row still failed to parse.

Catch it before the client does

The rule that saves the most embarrassment: never trust a date column you didn't convert yourself. Import turns dates to text more often than any other error, it's invisible at a glance, and it fails in the one place clients look first — the trend over time. A thirty-second alignment check at the start of every file costs you nothing and stops you shipping a quarter that appears to have happened in the wrong order.