How to Convert Text to Dates in Excel

To convert text to a date in Excel, you turn the text into the serial number Excel uses for dates, then format it. Text that matches your date settings converts with DATEVALUE or VALUE. A whole column converts with Text to Columns. Odd formats like 20240315 need DATE with LEFT, MID and RIGHT. This guide shows when to use each, so imported dates finally sort, filter and calculate.

How to tell if a date is stored as text#

A text date looks like a date but behaves like a word. Excel can't sort it in date order, filter it by month or subtract it from another date. Look for these signs:

Text dates usually come from CSV exports, other software or copy-and-paste from web pages. Sometimes Excel also shows a green triangle in the corner of the cell. Real dates are numbers underneath. If that idea is new, the comparison of TEXT and VALUE explains how Excel moves between text and numbers.

How to convert text to a date with DATEVALUE or VALUE#

Use DATEVALUE or VALUE when the text is in a format your Excel already understands. If A2 holds the text 15/03/2024 on a computer set to day-month-year dates:

=DATEVALUE(A2)

The result is 45366, the serial number for 15 March 2024. Format the cell as a date (Windows: Ctrl+1; Mac: ⌘1, then choose Date) and it displays as a date.

VALUE does the same job and also handles numbers stored as text:

=VALUE(A2)

A shorter trick is the double minus, =--A2, which forces Excel to treat the text as a number. All three work only when Excel can read the text as a date. The VALUE function guide has more examples, and the comparison of DATE and DATEVALUE explains when each one fits.

Both functions depend on your regional settings. On a computer set to US dates, 15/03/2024 fails, because there is no 15th month. Text written as 2024-03-15 is the safest input, because Excel recognises that order in most settings.

How to convert a whole column with Text to Columns#

Text to Columns converts the cells in place, with no helper column. It's the quickest fix for a column of imported dates:

1. Select the column of text dates.
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next twice.
4. Under Column data format, choose Date and pick the order your text uses, such as DMY or MDY.
5. Click Finish.

The DMY or MDY choice is what makes this powerful. You tell Excel how the text is written, whatever your computer's settings say. So a US export like 03/15/2024 converts correctly on a UK computer if you choose MDY.

If the dates use dots, like 15.03.2024, you can also try Find and Replace (Windows: Ctrl+H; Mac: ⌃H). Replace . with /. Excel re-reads each cell after the change, and cells that now match your date format become real dates.

How to convert unusual formats with DATE, LEFT, MID and RIGHT#

When Excel can't read the text at all, take it apart and rebuild it with DATE. DATE takes a year, a month and a day as numbers, in that order. LEFT, MID and RIGHT cut those pieces out of the text.

For 20240315 (year, month, day with no separators):

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

LEFT takes 2024, MID takes 03 starting at position 5, and RIGHT takes 15. DATE turns them into 15 March 2024. Excel converts the text pieces into numbers automatically.

Here are more patterns:

This approach never depends on regional settings, because you say exactly which part is the day and which is the month. That makes it the most reliable method for files shared between countries.

How to convert text dates with times#

If the text includes a time, such as 2024-03-15 14:30, VALUE usually converts both parts at once. The result is a number with a decimal, like 45366.6042, where the decimal is the time of day.

Format the cell with a custom format such as dd/mm/yyyy hh:mm to see both. To keep only the date, wrap it in INT, which drops the decimal: =INT(VALUE(A2)).

Common mistakes when converting text to dates#

Most failed conversions come from hidden characters, formats or regional settings. Check these:

1. The result is a number. That's correct. Format the cell as a date.
2. Extra spaces. A leading or trailing space makes DATEVALUE fail. Use =DATEVALUE(TRIM(A2)).
3. Day and month swapped. 03/04/2024 can be 3 April or 4 March. Check a date with a day above 12 to see which order the source uses, then use Text to Columns or DATE.
4. Two-digit years. Excel reads 24 as 2024, but 35 as 1935, because years 30 to 99 go into the 1900s by default. Use four-digit years where you can.
5. Formatting doesn't convert. Changing a text cell's format to Date does nothing. The value itself must be converted first.

Once your dates are real, you can calculate with them, for example adding days, months or years to a date.

Practise it#

Cleaning up imported dates is one of the most common real-world Excel tasks, and a favourite in job tests. Practise date basics in a live spreadsheet to get comfortable with DATE and serial numbers, then practise TEXT and VALUE to master converting between text and numbers. Each lesson checks your formula instantly. Or try the exercise on this page first.