To convert dates stored as text in Excel, select the cells and use Data > Text to Columns, then choose the date order that matches the text (such as DMY or MDY) and select Finish. For a formula-based conversion, use =DATEVALUE(A2) when A2 contains a date string Excel recognizes; then format the result as a date. The right method depends on the text’s pattern and regional settings, because the same string can mean different dates in different regions.
Why text dates cause problems
Excel stores real dates as serial numbers and displays those numbers using a date format. A text date may look identical on the screen, but Excel treats it as a sequence of characters. As a result, a text date may not sort chronologically, work correctly in date calculations, or behave as expected in filters and PivotTables. For example, text values may sort alphabetically, placing 10/02/2025 before 2/11/2024 even though that is not chronological order.
Text dates commonly appear after importing a CSV or copying data from a website, database, or another application. They can also arise when an import uses a different regional convention from your Excel settings. A cell’s appearance alone does not prove it contains a real date: changing its number format to Date changes how a number is displayed, but it does not convert ordinary text into a date value.
Convert a column with Text to Columns
For a consistent column of text dates, Text to Columns is often the quickest conversion. It can interpret a date according to a specified order instead of relying entirely on Excel to guess. In Microsoft Excel for Microsoft 365 on Windows:
- Select the cells containing the text dates. If the column has a heading, select the date cells rather than the heading.
- Choose Data > Text to Columns.
- In the wizard, select Delimited and choose Next. Leave the delimiter choices unchanged if you do not want to split the dates into separate columns.
- Choose Next again. Under Column data format, select Date and choose the order used in the source, such as DMY for day/month/year or MDY for month/day/year.
- Select Finish. Apply a date number format if you want a particular display, such as 31-Dec-2025.
Suppose the selected text is 31/12/2025. Choosing DMY tells Excel to read 31 as the day, 12 as the month, and 2025 as the year. Choosing MDY would be inappropriate because there is no thirteenth month. For a value such as 04/05/2025, however, both the month and day could be valid, so choosing the source order is essential.
Text to Columns writes converted values into the selected column, so keep a copy of the original data if you may need it. On Excel for Mac, the Text to Columns command is also on the Data tab; the wizard and its date-order choice are similar, although exact ribbon layout can vary by release. Excel for the web may not offer the same full wizard in every build. If you cannot find it there, open the workbook in the desktop app or use a formula.
Use DATEVALUE when Excel recognizes the date
DATEVALUE converts a date string that Excel can interpret into a date serial number. With text dates in A2:A100, enter this in B2 and fill it down:
With the text date in A2, use =DATEVALUE(A2). If A2 contains 12/31/2025 and the current regional settings recognize month/day/year, the result is the serial value for December 31, 2025; format B2 as a date to see a calendar date.
The function returns a number, not a specially formatted date. Select the formula results and use Home > Number Format, then choose a date format, or open Format Cells with Ctrl+1 on Windows. On Mac, Command+1 opens Format Cells. Once the values are converted, you can copy the results and use Paste Special > Values if you want to replace formulas with fixed date values.
DATEVALUE can return #VALUE! if the text is not recognizable, contains extra characters, or uses an order that conflicts with the system’s date settings. It is also intended for date text, not for extracting a date from an arbitrary sentence. If the original text includes a time as well, DATEVALUE returns the date portion rather than preserving the time. Clean or parse the source first if you need to retain time data.
Handle fixed date patterns with DATE
When every entry follows a known, fixed pattern, building a date from its year, month, and day can be more dependable than asking Excel to guess. For dates in A2:A100 written exactly as YYYY-MM-DD, such as 2025-12-31, enter this in B2 and fill down:
With a fixed YYYY-MM-DD string in A2, use =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))). For A2 containing 2025-12-31, the result is December 31, 2025.
This formula takes the first four characters as the year, the two characters starting at position six as the month, and the final two characters as the day. It assumes consistent positions and two-digit month and day fields. If the data contains extra spaces or different separators, clean or adjust the formula before filling it down.
For a fixed DD/MM/YYYY pattern, a different formula is needed. With A2 containing exactly 31/12/2025, use =DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2))). It returns December 31, 2025. These formulas deliberately assign the components, so they avoid interpreting 04/05/2025 according to the computer’s regional settings. They are not suitable for a column that mixes date orders or inconsistent text lengths.
Check regional settings and ambiguous dates
Excel’s interpretation of a date string can depend on regional settings. In a month/day/year setting, 03/04/2025 commonly means March 4; in a day/month/year setting, it commonly means 3 April. A string such as 19/04/2025 helps identify a day-first source because 19 cannot be a month, but a string such as 03/04/2025 does not reveal its intended meaning by itself.
Before converting a large dataset, check where it came from and establish the source convention. A format such as YYYY-MM-DD is less ambiguous to people and systems, but an imported string still needs conversion if Excel has retained it as text. Do not choose a date order based on which result looks plausible for just one row. Compare the source documentation or a known date, and check several records, including entries where the first two numbers are both 12 or less.
If a workbook uses a different date system, its displayed serial numbers may differ from those in another workbook. Excel supports 1900 and 1904 date systems, and linked or copied workbooks can be affected by that setting. Usually, users should work with the displayed dates rather than manually adjusting serial numbers. If dates shift by a consistent number of days after moving between workbooks, check the workbook date-system setting instead of altering the source text at random.
Clean spaces and other conversion obstacles
Leading or trailing spaces, nonbreaking spaces, apostrophes, and extra words can prevent conversion. A cell that appears to contain 2025-12-31 may actually contain spaces copied from a web page. If the date is surrounded by ordinary spaces, you can create a cleaned helper value. With the original text in A2, use =TRIM(A2) in B2, then use DATEVALUE on the cleaned result if Excel recognizes its date order. TRIM removes ordinary extra spaces but does not remove every invisible character, such as all nonbreaking spaces copied from web content.
To inspect a suspicious cell, click it and look at the formula bar, or test whether Excel treats it as a number. With a possibly converted value in B2, =ISNUMBER(B2) returns TRUE for a real date serial and FALSE for text. Since dates are numeric values in Excel, this is a useful check, though it does not by itself confirm that the date is the intended one. Review the displayed date and compare it with the source.
Also distinguish a date from a date-time value. Excel represents a date as a serial number and the time as a fractional part of a day. A format that shows only the date can hide a time fraction without removing it. Conversely, DATEVALUE discards the time portion of a recognized date-time string. If retaining hours and minutes matters, use a conversion that recognizes the full date and time, and apply a format that displays both.
Convert dates during a repeatable import
If you receive the same kind of CSV or text file regularly, Power Query can make conversion repeatable. In Excel for Microsoft 365 on Windows, use Data > From Text/CSV to preview and load the file. In the Power Query Editor, select the date column and use Transform > Data Type > Using Locale. Choose Date as the data type and select the locale that matches the source convention, then choose Close & Load. This explicit locale selection is especially useful when the source date order differs from the computer’s regional settings.
Power Query is useful for larger files and multi-step cleanup because it records transformations and can repeat them when refreshed. It does not remove the need to verify the source format: choosing the wrong locale can still produce an incorrect interpretation or an error. The interface can differ somewhat between Windows, Mac, and Excel for the web, and the available connectors and editing features depend on the version. If the Using Locale command is unavailable in your build, convert the column in desktop Excel or use a formula that explicitly parses its components.
Verify results and choose a method
After conversion, check that the result is a real date and that it means what the source intended. Apply a format such as yyyy-mm-dd to make the order easy to inspect, then sort a sample from oldest to newest. A correct chronological sort is a helpful check, but it is not proof that ambiguous values were interpreted correctly.
- Use Text to Columns when you have one column of consistent text dates and can specify its date order.
- Use DATEVALUE for recognizable text dates, especially when a simple formula and fill-down workflow is convenient.
- Use DATE with text-extraction functions when the source pattern is fixed and you need explicit control of year, month, and day.
- Use Power Query when importing recurring files or applying the same cleanup to many rows.
For a one-time conversion, keep the original column until you have checked the results. For ongoing work, preserve the source data and document its date convention so someone refreshing or updating the workbook does not have to guess. Formatting should come last: first convert the underlying value, then choose how the date should look.
Frequently Asked Questions
Why does Excel show a date but not calculate with it?
The cell may contain text that only looks like a date. Convert it with Text to Columns or DATEVALUE, then verify with ISNUMBER and test a date calculation. A date number format alone does not turn text into a date.
How do I convert text such as 2025-12-31 in Excel?
If Excel recognizes the ISO-style date, DATEVALUE may work. For a consistent YYYY-MM-DD string, a DATE formula that extracts the year, month, and day explicitly avoids dependence on regional interpretation.
How do I change DD/MM/YYYY text to a real date?
In Text to Columns, select Date and choose DMY. Alternatively, if every value has the exact DD/MM/YYYY pattern, use a DATE formula that assigns the year, month, and day components directly.
Why does DATEVALUE return #VALUE!?
Excel may not recognize the text under the current regional settings, or the cell may include extra spaces, hidden characters, or non-date text. Check the source pattern, clean the value, or parse its components with DATE.
Can I convert text dates without a formula?
Yes. Select the cells, choose Data > Text to Columns, select Date, choose the source order, and finish. For recurring imports, Power Query can apply a conversion using a selected locale.
Related
- Comparison Table Excel Template: Build, Format, and Use
- Comparison Excel Sheet Template: Build a Clear Side-by-Side
- Comparison Plot in Excel With R²: Steps and Meaning
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Convert Dates to Text in Excel: Formulas and MethodsIf the dates are real Excel dates in A2:A1000, enter =TEXT(A2,"mmmm d, yyyy") in B2 and fill it…
- Excel Attendance Percentage: Formula and ExamplesTo calculate attendance percentage in Excel, divide the number of sessions marked present by the number of recorded…
- Excel Midpoint of Dates: Formula and ExamplesTo find the midpoint between two dates in Excel, put the earlier date in A2 and the later…
- How to Copy a Formula Across an Entire Row in ExcelTo copy a formula across a row in Excel, enter it in the leftmost cell, select that cell…
- Filter an Excel Table by Column: Steps, Tips & FixesTo filter an Excel table by column in Microsoft 365 for Windows, select the table, open the drop-down…
- Excel Formula for Highest Value: MAX and Other OptionsTo find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.