To convert a text date in Excel, enter =DATEVALUE(A2) in an empty cell, then apply a Date format to the result. The DATEVALUE function works when A2 contains date text Excel recognizes under your regional settings. It returns a numeric date serial, which Excel can use for sorting and calculations.
If your imported text includes a weekday, time, or other extra information, you may need to extract the date first. The fixed-pattern example below shows how to do that with MID and RIGHT.
Convert a text date with DATEVALUE
Suppose A2 contains the text Jan 15, 2010. In B2, enter:
=DATEVALUE(A2)
With B2 formatted as General, the result is 40193 in a workbook using the 1900 date system. Apply a Date format to show January 15, 2010 instead.
| Text in A2 | Formula in B2 | Serial number (1900 system) | Date represented |
|---|---|---|---|
Jan 15, 2010 | =DATEVALUE(A2) | 40193 | January 15, 2010 |
Jun 30, 2010 | =DATEVALUE(A2) | 40359 | June 30, 2010 |
These examples assume Excel recognizes English month abbreviations. To try them as text, type a leading apostrophe, such as 'Jan 15, 2010; Excel uses the apostrophe to mark a text entry and does not display it in the cell.
Changing the format of the original text cell alone does not convert its contents. Convert the text first, then format the numeric result. This is the workflow in Microsoft's guide to converting dates stored as text.
Extract a date from a longer text string
For a database export with a consistent layout, the original formula from this article remains useful. This is the same source pattern discussed in extracting a date from text in Excel.
Suppose A2 contains this exact text:
Wed Jun 30 08:00:01 GMT 2010
Enter this formula in B2:
=--(MID(A2,5,6)&", "&RIGHT(A2,4))
The result is 40359 in the 1900 date system. Here is how the formula builds it:
| Part | Result | What it does |
|---|---|---|
MID(A2,5,6) | Jun 30 | Extracts six characters starting at character 5. |
RIGHT(A2,4) | 2010 | Extracts the final four characters. |
MID(A2,5,6)&", "&RIGHT(A2,4) | Jun 30, 2010 | Joins the pieces with a comma and space. |
--(...) | 40359 | Converts the recognized date text into a number through double negation. |
The MID function counts characters from 1, and the RIGHT function starts at the end of the text. The ampersand (&) joins the extracted pieces. The first minus sign coerces the date text into a negative number; the second makes it positive again.
Use this formula only when the month and day occupy characters 5–10 and the four-digit year is at the end. Extra leading spaces, a different date order, or trailing text can break those assumptions. Excel must also recognize the assembled month name under your regional settings.
This formula extracts the written calendar date and discards the time and GMT label. It does not perform a time-zone conversion.
Fill down and format the results as dates
Once the formula works in B2:
- Drag B2's fill handle down through the rows containing source text. Each copied formula will refer to the corresponding row in column A.
- Select the result cells. On the Home tab, open the Number Format list and choose Short Date or Long Date.
- In desktop Excel, for more choices, right-click the selected cells and choose Format Cells. Open Number → Date, choose a format, and click OK. The shortcut is Ctrl+1 on Windows or Command+1 on Mac.
See Microsoft's date-formatting instructions for platform-specific options. In Excel for the web, use the ribbon's Number Format choices.
The older Windows screenshot below shows the Date category for the extracted June 30 example:
After formatting and filling down, the results look like this:
Formatting changes the display; the underlying numbers remain available for calculations. If you want to remove the formulas later, copy the results and use Paste Special → Values before deleting their source text.
Why does the conversion fail or return the wrong date?
Check the source's date order. Text such as 03/04/2026 can mean March 4 or April 3. A successful conversion does not guarantee the intended date. Compare a few results with the source and check your regional date settings.
Check unrecognized text. A #VALUE! error can mean Excel cannot interpret the date text. For the longer timestamp above, extract the known date positions first. For other layouts, inspect the source before reusing that formula. If you use Text to Columns in desktop Excel, choose a Date column format that matches the source order, such as DMY or MDY; Microsoft's Text Import Wizard guide explains these choices.
Include the year and check whether you need the time. DATEVALUE uses the computer's current year when the year is omitted, and it ignores time information in recognized date text. Include a four-digit year for repeatable results. See Microsoft's DATEVALUE reference for these behaviors.
Why do serial numbers differ between workbooks?
Excel supports the 1900 and 1904 date systems. The same date has serial numbers that differ by 1,462: January 15, 2010 is 40193 in the 1900 system and 38731 in the 1904 system.
All serial examples above use the 1900 system. If dates shift when moving data between workbooks, especially older Mac files, check both workbooks' date-system settings before adjusting the data. Microsoft's date-systems guide explains the difference and where to find the setting.