How to convert text to a date in Excel

First check: is it a date or text?

Column B was pasted together from six systems. Every row except B4 (Oct 23) means October 5, 2026, written six different ways. Only B9, typed into Excel, is a real date. The rest are text that looks like a date, so sorting, SUMIFS date ranges and date math either fail or give wrong answers.

ISNUMBER is the definite test, because a real date is a number underneath. Text dates are also left-aligned by default and ignore any date format you apply. To count how many cells in a column are real dates, use =SUMPRODUCT(--ISNUMBER(B2:B9)), which returns 1 here.

Method 1: DATEVALUE or a double minus

DATEVALUE reads date text using your computer's regional settings and returns the date's serial number. On a US-English system it handles 10/5/2026, 2026-10-05, Oct 5, 2026 and 5-Oct-2026. Excel shows the result as a number like 46300 until you format the cell as Short Date.

A double minus does the same job in shorter form: =--B5 forces Excel to convert the text to a number, which for date text means a date. VALUE(B5) works too. One difference: if the text contains a time, DATEVALUE drops it and VALUE or -- keeps it.

The silent trap: day-first text on a month-first system

The UK supplier sends 05/10/2026, meaning 5 October. A US-English Excel reads text as month/day/year, so DATEVALUE happily returns May 10, 2026. No error, no warning, wrong date. B4, 23/10/2026, does fail with #VALUE! because there is no month 23. A column with some errors and some "working" rows is the classic sign that every row is being read in the wrong order.

The reverse happens too: a UK or European Excel will read US text like 10/5/2026 as 10 May. DATEVALUE is only safe when the text order matches the computer it runs on.

Method 2: DATE with LEFT, MID and RIGHT (works on any system)

When you know the order of the text, pull out each part and give it to DATE(year, month, day). Nothing depends on regional settings, so the workbook gives the same answer in New York and London. For dd/mm/yyyy, the year is the last 4 characters, the month starts at character 4, and the day is the first 2.

For yyyymmdd from payroll and banking systems (B7), the parts are in a different place: =DATE(LEFT(B7,4),MID(B7,5,2),RIGHT(B7,2)) returns Oct 5, 2026. This also works when 20261005 arrived as a number rather than text, because LEFT and MID treat numbers as text.

When the day and month are not always two digits

Fixed positions break on text like 5.10.2026, where the day has one digit. In Excel 365 and 2024, TEXTBEFORE and TEXTAFTER split on the separator instead of counting characters, so 5.10.2026 and 15.10.2026 both work.

Do not just replace the dots with slashes and use DATEVALUE. =DATEVALUE(SUBSTITUTE(B8,".","/")) on a US system turns 5.10.2026 into May 10 for the same reason as above. In older Excel, Text to Columns with DMY (below) is the easier route.

Columns with some real dates and some text

A column that mixes typed dates with pasted text is common after a few months of copy and paste. DATEVALUE returns #VALUE! when the cell is already a real date, because it only accepts text. Test first and pass real dates through untouched.

Method 3: Text to Columns (whole column, no formulas)

To convert a column in place, Text to Columns is the fastest way, and its date option lets you say what order the text is in. That makes it the right tool for dd/mm text on a US system, and the reverse. It works in every version of Excel. Select one column at a time.

1. Select the text dates, for example B3:B4.
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next.
4. Untick every delimiter and click Next.
5. Under Column data format choose Date, then pick the order the text is in: DMY for 05/10/2026, MDY for 10/5/2026, YMD for 2026-10-05 or 20261005.
6. Click Finish, then format the column as Short Date.

Method 4: Power Query for imports you repeat

If the same export arrives every week, set the conversion up once in Power Query. Select the data and choose Data > From Table/Range. Right-click the date column, choose Change Type > Using Locale, pick Date and the locale the file came from, such as English (United Kingdom), and click OK. Then Home > Close & Load. Next week, paste the new data in and click Data > Refresh All.