Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallExcel may show something that looks like a date but store it as text. That often happens when a value is pasted or imported, entered in a cell formatted as Text, contains extra spaces, or uses a day/month order Excel does not expect. Convert the text into a real date value first; changing the cell’s display format alone will not reliably do that.
First, check whether Excel has a date value or text
Excel stores dates as sequential numbers so it can use them in calculations. A text string that resembles a date is not necessarily one of those values, so date subtraction, sorting, filtering, or date functions may behave unexpectedly.
As an Amazon Associate I earn from qualifying purchases.
Under Excel’s default alignment, a text-formatted date is often left-aligned and a date value is generally right-aligned. Alignment is only a clue: it can be changed manually, so do not treat it as proof. Also look for leading spaces in pasted or imported values. If a date calculation returns #VALUE!, check that both inputs are valid dates and that their date conventions match.
Choose a conversion method that matches the text
| Input | Best-fit method | Important limitation |
|---|---|---|
| A text date in a format Excel recognizes | DATEVALUE |
Parsing depends on Excel recognizing the format and date order. |
| A fixed, known character pattern | DATE with text functions |
Character positions must match the actual input exactly. |
| A consistently structured column | Text to Columns | Select the source date order; verify the results before replacing data. |
| Recurring or imported data | Power Query: Change Type > Using Locale | Choose the locale that matches the source data. |
Convert a recognizable text date with DATEVALUE
If A1 contains a text date Excel can interpret, enter this in a blank cell:
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
=DATEVALUE(A1)
- Set the formula cell to General and enter the formula.
- Check that the result represents the intended date. If it displays as a number, that can be the date’s serial value; apply a date number format to display it as a date.
- Fill the formula down if needed. If you want to replace the original text, copy the verified results and use Paste Special > Values.
DATEVALUE is not universal: it works when Excel recognizes the text as a date. If it returns an error or an unexpected date, inspect the characters and the source’s day/month convention rather than repeatedly changing the display format. Excel’s VALUE function has the same general constraint: it converts only text in a format Excel recognizes.
Build a date from a fixed text pattern
When the layout is known, extract the year, month, and day and pass them to DATE. For an eight-character YYYYMMDD value in A1, use:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
For a fixed dd/mm/yyyy value with exactly two day characters, two month characters, and four year characters, use:
=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
These formulas rely on the stated character positions. If your source has a different pattern, adjust the extraction positions; do not apply either formula blindly to variable-width dates.
Convert a consistent column with Text to Columns
For a column of similarly structured text dates, Text to Columns can convert the values in bulk:
- Select the date column, then open Data > Text to Columns.
- In the wizard, set the column data format to Date.
- Choose the order matching the source text, such as YMD for year-month-day values, and complete the wizard.
- Check a sample of the converted results before overwriting or discarding the original values.
Take particular care with values where both the day and month are 12 or lower: either order can look plausible while representing a different date. If the source convention is unknown, determine it before converting.
Rank #4
Set the locale for imports and recurring data
For repeated imports, use an explicit locale rather than relying on whichever regional settings happen to be active. In Power Query, select the column, then choose Change Type > Using Locale and specify the data type and the locale that matches the source.
Power Query applies the column’s Change Type setting first, then its own locale, then the operating-system locale when interpreting values. A workbook query retains the locale selected by its author or last saver, which helps keep interpretation consistent across users.
For a Text Import Wizard import, select the date order that matches the incoming text. A mismatched order or a column containing mixed formats may leave values as General instead of converting them as intended; inspect the imported results.
Apply a date format after conversion
Once the cell contains a real date value, choose Short Date, Long Date, or a custom date format. Date and time display formats can vary by locale; formats marked with an asterisk respond to system regional date and time settings. If a converted value appears as a number, apply a date format. If the cell shows #####, widen the column.
Formatting controls how a value appears, not whether arbitrary text has been converted into a usable date. If the underlying value is still text, return to the appropriate conversion method above.
Free tools Windows power users keep installed
One-click scans. No signup required.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
