Text to Date in Excel: Convert Dates Stored as Text

How-to basics · Updated November 18, 2026

To convert a text date in cell A2 into an Excel date, use =DATEVALUE(A2) if Excel recognizes the date format in your regional settings; for example, text reading March 15, 2024 becomes a real date representing March 15, 2024. If the result appears as a number, format the result cell as a date. For a whole column, Data > Text to Columns can often convert the values without a formula.

What a text date means in Excel

Excel can display dates, but it stores recognized dates as numbers. In the standard 1900 date system, a date is a serial number counting days from a starting point; its displayed appearance comes from the cell’s number format. This is why changing a cell’s format to Date can make a real date serial look like a date, but cannot by itself turn ordinary text into a date value.

A date stored as text may look completely normal in a worksheet. For example, 3/15/2024 could be text characters, while a cell containing a numeric date serial might display the same way. The difference matters when you sort, filter, calculate elapsed days, or use the value in formulas. Text can sort separately from real dates, and date arithmetic may fail or produce unexpected results.

Text can enter a workbook when you paste from a website, receive a CSV file, import data from another system, or set a column to Text before entering values. A leading apostrophe can also tell Excel to retain an entry as text. The apostrophe usually does not appear in the cell itself, but it can be seen in the formula bar.

How to check whether a date is text

Start with a cell that looks suspicious. Select it and inspect the formula bar and the Home tab’s Number Format box. A format of Text is a clue, but formatting alone is not proof: a cell can be formatted as Date while still containing text. Likewise, left alignment is a useful hint, not a reliable test, because alignment can be changed manually.

A practical check is to try a date calculation in an empty cell, or sort a small sample from oldest to newest and see whether the values behave chronologically. If Excel treats the entry as text, calculations such as subtracting one date from another may return an error or an unexpected result. For a formula-based check, use ISNUMBER: actual Excel dates are numeric values, while text dates are not. The formula below assumes the value being checked is in A2; it returns TRUE for a numeric date serial and FALSE for text.

With the value to check in A2, enter =ISNUMBER(A2). For a genuine Excel date, the result is TRUE; for a text entry such as an unconverted date string, the result is FALSE.

Do not rely on appearance alone. A cell showing 15-Mar-2024 may be a real date or text that happens to resemble one. Testing the underlying value is more dependable than judging how it looks.

Convert a column with Text to Columns

For a simple list, Text to Columns is often the fastest built-in conversion method. It can interpret text dates according to a date order you select, without splitting the values into multiple columns. First make a copy of the source column or save a backup if the data is important; this operation changes the selected cells.

  1. Select the cells containing the text dates. Include only the rows you intend to convert, and avoid selecting unrelated columns.
  2. On Windows, choose Data > Text to Columns. On Mac, the command is also available from the Data tab in current desktop Excel versions.
  3. In the wizard, choose Delimited and select Next. Leave delimiters unchecked if you do not want the date text split into separate fields, then select Next again.
  4. Under Column data format, choose Date and select the order that matches the source text, such as MDY, DMY, or YMD.
  5. Check the destination if you need to preserve the original column, then select Finish. Apply a date format if the resulting dates do not display in the style you want.

The date-order choice is essential. If the source contains 04/05/2024, choosing MDY means April 5, while DMY means 4 May. Excel cannot infer which meaning the source author intended when both first numbers are 12 or lower. Check a known example before converting the full list.

Use DATEVALUE for dates Excel recognizes

DATEVALUE converts a date written as text into Excel’s date serial, provided Excel can interpret the text under the computer’s regional date settings. It is useful when you want to preserve the original column and create converted values beside it. Put the formula in a new column, fill it down, and review a few results before relying on them.

With text dates in A2:A100, enter =DATEVALUE(A2) in B2 and fill down. If A2 contains March 15, 2024 in a format Excel recognizes, the result represents March 15, 2024; format B2 as a date to display it that way.

DATEVALUE does not solve every import problem. A text value using a different regional convention may be rejected or interpreted as another date. For instance, 05/06/2024 is ambiguous without knowing whether the source uses month/day/year or day/month/year. Also, DATEVALUE expects date text; it is not a general-purpose cleanup tool for every mixture of dates, labels, and extra characters.

If the converted dates are correct, you can keep the formula results or replace them with fixed values. To replace formulas while retaining their results, copy the converted cells, then use Home > Paste > Paste Values. Confirm the output first, because pasted values will no longer update if the original text changes.

Handle fixed-format dates such as YYYY-MM-DD

Some sources provide dates in a consistent year-month-day pattern, such as 2024-03-15. DATEVALUE may recognize that pattern on many systems, but parsing can depend on regional settings and the source’s exact format. When every entry has the same fixed layout, constructing the date from its year, month, and day components avoids relying on the computer’s interpretation of slash-separated dates.

With text in A2 formatted exactly as four year digits, a hyphen, two month digits, a hyphen, and two day digits, enter =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))). For A2 containing 2024-03-15, the result is March 15, 2024; format the formula cell as a date to show a date rather than its serial number.

This method assumes the text always follows that precise pattern and contains a valid calendar date. It is not appropriate for variable-length dates, dates with time zones, or strings that include extra words without further parsing. Excel’s DATE function can normalize out-of-range components, so a formula result alone does not prove that an input was valid. Check the source data for impossible dates before using the converted column.

Formatting is not the same as conversion

A common misconception is that selecting Home > Number Format > Short Date converts any date-looking entry. That command changes how numeric values are displayed. If the underlying value is text, applying a date format may leave it looking exactly as it did before, because Excel still has text to display rather than a date serial to format.

After conversion, a result may appear as a number such as 45366. That does not necessarily mean the conversion failed: it may be the date serial shown with General or Number formatting. Select the result and choose a date style from the Number Format list on the Home tab. Conversely, if a value remains text, changing its number format to Short Date will not make it usable in date calculations.

It is also useful to distinguish a date from a date-and-time value. Excel stores a time as a fractional part of a day, so a converted value may include a time even when the cell format hides it. If the source contains time information, choosing a date-only format can conceal the time without removing it. Check the formula bar or apply a date-and-time format if the time matters.

Regional settings and imported data

Date interpretation varies by region. A source file may use DMY, MDY, or YMD order, and a text date that is clear in one locale may be ambiguous in another. When importing a CSV, inspect several known dates and identify the source convention before converting. Do not assume that Excel’s current locale matches the system that created the file.

For recurring imports, Power Query can offer a more repeatable approach. In Microsoft 365 for Windows, import the file through Data > From Text/CSV, then use the Power Query editor to set the column’s data type and, when needed, specify a locale for the conversion. Menu labels and available controls can vary somewhat by Excel build, but the key is to set the type with the source locale in mind rather than relying on automatic detection. If the same file is refreshed regularly, save the query steps so the transformation can be applied again.

Excel workbooks can use either the 1900 or 1904 date system. Most Windows workbooks use the 1900 system; some older Mac workbooks may use the 1904 system. When dates are copied between workbooks using different systems, their serial values can differ by 1,462 days. This is a workbook compatibility issue rather than a text-conversion method, but it is worth checking if converted dates shift by several years after moving data between files.

Common conversion problems and fixes

  • The result is a number: The value may already be a valid date serial. Apply a date number format to display it as a calendar date.
  • The formula returns an error: Check for spaces, extra punctuation, nonbreaking spaces, or a format DATEVALUE does not recognize. Clean the source or use a parsing method that matches its structure.
  • The date changes to the wrong month and day: Check whether the source uses MDY or DMY, then use the correct Text to Columns date order or an explicit parsing method.
  • Only some rows convert: The column may contain mixed formats, blank cells, headings, or invalid dates. Sort or filter the source and handle each distinct pattern separately.
  • The output seems unchanged: Test the result with ISNUMBER and check whether you are looking at the original text column rather than the converted output.

When a conversion affects many records, compare the new dates against a few trusted source examples, including dates near the beginning and end of the range. Look for blanks, errors, and unexpected years. Keep the original text until the converted values have been checked; it is useful evidence if an ambiguous date needs to be corrected.

Frequently Asked Questions

Why does Excel show a date as a number?
Excel stores recognized dates as serial numbers. If the cell uses General or Number formatting, you may see the serial instead of a calendar date. Choose a date format to change its display.

Can I convert text dates without a formula?
Yes. Select the cells and use Data > Text to Columns, then choose Date and the correct source order in the wizard. Check the result because ambiguous dates can be interpreted differently.

Does changing the cell format convert text to a date?
No. Formatting changes the display of a value that Excel already recognizes as numeric. Use Text to Columns, DATEVALUE, or a suitable parsing method to convert text first.

Why does DATEVALUE work on one computer but not another?
DATEVALUE depends on the date text and regional settings. A format accepted under one locale may be ambiguous or unrecognized under another. Use the source’s known date order or specify a locale during import.

How can I tell if a converted value is a real Excel date?
Use ISNUMBER on the result cell. TRUE indicates a numeric value, which includes Excel dates; then verify that the displayed date matches the source, since a number could also be something other than a date.

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.