How to Change Text to Dates in Excel

How-to basics · Updated November 20, 2026

To change text to dates in Excel, select the cells and use Data > Text to Columns, then choose the date order that matches the text and click Finish. For a repeatable conversion, use =DATEVALUE(A2) when A2 contains a date Excel recognizes; then format the result as a date.

Why text that looks like a date may not be a date

Excel stores a real date as a number, often called a serial value, and displays that number using a date format. The display can look like 3/15/2024, 15-Mar-24, or another style, while the underlying value remains usable for sorting, date arithmetic, and functions such as YEAR and MONTH. Text that merely looks like a date is different: Excel may treat it as a sequence of characters, so sorting can be alphabetical and subtracting dates may produce an error.

This often happens after pasting data from a website, opening a CSV file, or importing a report created in another region. For example, a cell containing the characters 2024-03-15 may be interpreted as text if it was imported with the Text data type. Changing the cell’s number format alone does not convert those characters; it only changes how a genuine numeric date is displayed. First convert the value, then choose how you want the date to look.

Convert a column with Text to Columns

Text to Columns is a useful one-time conversion for a column of consistently formatted dates. In Excel for Microsoft 365 on Windows, select the cells and follow these steps:

  1. Choose Data > Text to Columns.
  2. In the wizard, select Delimited and click Next. Leave the delimiter boxes cleared if the values are not divided into fields by a delimiter, then click Next.
  3. Under Column data format, choose Date and select the order that matches the text, such as YMD for 2024-03-15 or DMY for 15/03/2024.
  4. Click Finish. If Excel asks whether to replace the contents of the destination cells, confirm only if overwriting those cells is intended.

After conversion, apply a display format if needed: select the cells, go to Home > Number Format, and choose Short Date or Long Date. The choice in the wizard is important. If the source is 04/05/2024, selecting MDY means April 5, while DMY means 4 May. If the order is uncertain, check the source system or a date whose day number is greater than 12 before converting the whole column.

On Excel for Mac, Text to Columns is also available from the Data tab, though the wizard’s layout can differ slightly by release. Excel for the web may not offer the same full Text to Columns workflow; use a formula or open the workbook in the desktop app if that command is unavailable.

Use DATEVALUE in a formula

DATEVALUE converts a date written as text into Excel’s date value when Excel can interpret the text under the workbook’s regional settings. Suppose the text date is in A2. In B2, enter =DATEVALUE(A2), then fill the formula down for the other rows. If A2 contains 3/15/2024 and the local settings recognize that as March 15, the result is a date value for March 15, 2024. Format column B as a date to see a familiar date display.

DATEVALUE is useful when the source text varies in length but follows a format Excel recognizes. It does not reliably resolve ambiguous dates: 03/04/2024 could mean March 4 or April 3, depending on the expected order and locale. Check the meaning before converting. The function can also fail when a cell contains extra words or an unsupported date pattern. Remove unwanted text first, or use a more explicit formula for a fixed structure.

For text consistently written as YYYY-MM-DD, a formula can construct the date from its year, month, and day positions instead of relying on regional interpretation. With an exact eight-digit-and-hyphen pattern such as 2024-03-15 in A2, use =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)) in B2. It returns the date March 15, 2024. This assumes every input has the same YYYY-MM-DD structure; it is not a general-purpose parser for mixed formats.

Keep the original text column until you have checked the converted results. Once the values are verified, you can copy the formula results and use Paste Special > Values if you want fixed dates rather than formulas linked to the source cells.

Convert dates during import with Power Query

For recurring files or large datasets, Power Query is often safer than repeating manual conversions. In Excel for Microsoft 365 on Windows, use Data > From Text/CSV to bring in a CSV file, then choose Transform Data if you need to inspect or adjust the import before loading it. In the Power Query Editor, select the date column and use Transform > Data Type > Using Locale. Set the data type to Date and choose the locale that matches the source, then select Home > Close & Load.

Using a locale makes the intended order explicit. A file from a system that writes dates as day/month/year can be interpreted using the appropriate locale even if the computer normally expects month/day/year. This helps prevent a valid-looking but incorrect conversion. Power Query also records the transformation steps, so you can refresh the query when a replacement file arrives, provided its structure remains compatible.

Power Query is available in Excel for Microsoft 365 on Windows, but exact import options can vary by platform and update. If the Using Locale command is not present in your version, convert the column in the desktop app with Text to Columns or use a formula that matches the source structure.

Format the converted dates and verify the result

Once Excel recognizes a value as a date, formatting controls its appearance, not the underlying date. Select the converted cells and choose a format from Home > Number Format. To specify a custom display, open the Format Cells dialog with Ctrl+1 on Windows, choose the Number tab, and select Date or Custom. For example, a custom format such as yyyy-mm-dd displays a date in year-month-day order. On Mac, use Command+1 to open Format Cells.

Check a conversion rather than relying only on appearance. One simple test is to change a sample cell temporarily to General using Home > Number Format > General. A real date will show a number rather than the original date text. You can also test a date calculation: if A2 is the converted date, =A2+1 should return the following calendar day. With A2 equal to March 15, 2024, the result is March 16, 2024. Undo or restore the display format after checking.

For a second check, sort the converted column from oldest to newest and inspect a few results against the source. A column that sorts in calendar order and supports calculations is more likely to contain real date values, but verification is still necessary when the original format was ambiguous.

Handle inconsistent formats and common errors

Mixed date formats require more care than a clean column. If some rows use 2024-03-15, others use 15/03/2024, and others include a time, one Text to Columns setting may not interpret every row correctly. Separate the formats first, standardize the source, or use Power Query steps tailored to the different patterns. A formula designed for one fixed pattern can silently produce wrong results when applied to another.

  • The cell still looks unchanged: check whether conversion actually occurred. A date format does not turn text into a date. Try General format or a calculation test.
  • You see a number such as 45366: that may be the valid date serial displayed with General format. Apply a date format rather than converting it again.
  • You see a row of number signs: the column may be too narrow to show the formatted value. Widen the column before assuming conversion failed.
  • You get #VALUE! from DATEVALUE: Excel may not recognize the text, or the cell may include extra characters. Check spaces, punctuation, month names, and the expected locale.
  • The date changes to the wrong month and day: the source order was likely interpreted incorrectly. Compare with the original data and reconvert using the right order or locale.

Leading or trailing spaces can interfere with conversion. If the only issue is surrounding spaces, a helper formula can clean them before DATEVALUE. With the text date in A2, enter =DATEVALUE(TRIM(A2)) in B2. This returns the date only if the cleaned text is still a format Excel recognizes; TRIM does not translate unfamiliar month names or decide ambiguous day and month order.

Choose the right conversion method

Use Text to Columns when you need to convert a consistent, already-present column once and know its date order. Use DATEVALUE when the input follows a pattern Excel recognizes and you want a formula-based result that can be filled down. Use an explicit DATE construction for a predictable fixed pattern such as YYYY-MM-DD. Choose Power Query when files arrive repeatedly, contain many rows, or need a documented import process with a specified locale.

In every case, keep a copy of the source values until the results are checked. The central question is not just whether Excel displays something date-like, but whether it has interpreted the correct calendar day as a date value. That distinction matters for sorting, filtering, calculating elapsed days, and grouping records by month or year.

Frequently Asked Questions

Can I convert text to dates without a formula?
Yes. Select the cells, open Data > Text to Columns, choose Date under Column data format, set the source order, and finish the wizard. Then apply a date display format if necessary.

Why does changing the cell format not convert the text?
Number formats affect values Excel already recognizes as numbers or dates. Text remains text when you apply a date format, so convert it first with Text to Columns, DATEVALUE, or an import transformation.

How do I convert text such as 2024-03-15?
Try Text to Columns with the YMD date order or use =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)) when A2 always follows that exact pattern. Format the result as a date.

Why is Excel changing 03/04/2024 to the wrong date?
The text is ambiguous: it can mean March 4 or April 3. Convert it using the source’s known date order or locale, and verify against the original data rather than relying on the computer’s default settings.

Can I convert dates in a CSV without changing the source file?
Yes. Import the CSV through Data > From Text/CSV and set the date column’s type and locale in Power Query before loading it. This keeps the original file separate and lets you repeat the transformation on later imports.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.