Excel DATEVALUE Not Working? Fix Common Date Errors

If Excel’s DATEVALUE formula is not working, the usual cause is that the cell does not contain date text Excel can interpret under your regional settings. Check whether the input is text, remove hidden spaces, and use a format that matches the text; if the cell already contains a real Excel date, you usually do not need DATEVALUE at all.

What DATEVALUE Does—and What It Does Not Do

DATEVALUE converts a date written as text into an Excel date serial number. Excel stores dates as numbers so it can sort them, calculate elapsed days, and use them in other date formulas. A number format can display that serial as a familiar date, but changing a cell’s appearance alone does not turn date-like text into a usable date.

For example, with the text March 14, 2025 in A2, the formula =DATEVALUE(A2) returns 45730 in a workbook using Excel’s standard 1900 date system. Apply a date format to the result cell to display it as March 14, 2025 or another date style. The number can look unexpected, but it is not an error: it is the stored date value.

DATEVALUE is specifically for text. If A2 already contains a real Excel date, it is already numeric, so applying DATEVALUE may produce #VALUE! rather than help. Date and time values are numbers too; formatting controls their display, not their underlying type.

Start by Identifying the Cell’s Actual Contents

Look at the source cell and test its type before changing the formula. A date that appears left-aligned may be text in a default-formatted cell, while a date that appears right-aligned is often numeric, though alignment can be changed manually and is not conclusive.

With the source value in A2, =ISTEXT(A2) returns TRUE if Excel treats it as text.
With the source value in A2, =ISNUMBER(A2) returns TRUE for a real Excel date or date-time number.
With the source value in A2, =DATEVALUE(A2) attempts to convert text that Excel recognizes as a date.

These checks help distinguish a parsing problem from a display problem. If ISNUMBER returns TRUE, format the cell as a date rather than converting it again. On Excel for Microsoft 365 for Windows, select the cell and choose Home > Number Format, then select a date format. The Mac ribbon is similar, though controls can be arranged differently. In Excel for the web, use the number-format control on the Home tab.

Check Regional Settings and Ambiguous Dates

Date text is interpreted according to regional conventions. The string 04/05/2025 could mean April 5 or May 4, depending on whether the expected order is month/day/year or day/month/year. DATEVALUE cannot infer which meaning you intended; it follows the locale and parsing rules available to Excel.

Use dates with a written month, such as March 14, 2025, when entering text manually in an English-language setup. For imported data, confirm the source’s date order before converting a whole column. A conversion that succeeds can still be wrong if Excel has silently read day and month in reverse.

Windows regional preferences can affect date interpretation. Review Settings > Time & language > Language & region if dates from another locale are being read unexpectedly. The exact regional setting may be controlled by an organization. Excel’s formula argument separator is a separate setting: the examples here use commas as required for standard English Excel syntax.

Fix Hidden Spaces and Nonprinting Characters

Copied values from websites, PDFs, reports, and exported systems may contain leading or trailing spaces, line breaks, or nonbreaking spaces. A cell can look like March 14, 2025 while containing characters that prevent DATEVALUE from recognizing it. Ordinary TRIM removes extra standard spaces, but it does not remove every kind of invisible character.

With unwanted nonbreaking spaces in the text in A2, enter =DATEVALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) in another cell. For an input that represents March 14, 2025 and has only such extra spaces around it, the result is 45730 in the standard 1900 date system. SUBSTITUTE replaces character 160 with a regular space, and TRIM removes extra regular spaces at the ends and between words.

If that still fails, inspect the source by editing the cell or using the formula bar, and check whether the text contains other characters or an unexpected format. CLEAN can remove many nonprinting characters, but no single cleanup formula handles every possible encoding or source-system problem. Keep the original column until you have verified the converted dates.

Convert Fixed-Format Text Without DATEVALUE

When every entry uses the same predictable layout, constructing a date from its year, month, and day can avoid regional interpretation. This is useful for text in ISO-style year-month-day form, such as 2025-03-14, when Excel’s locale does not parse it as expected.

With the exact text 2025-03-14 in A2, use =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). The result is March 14, 2025, stored as 45730 in the standard 1900 date system. This formula assumes that each value has four year digits, a hyphen, two month digits, another hyphen, and two day digits. It is not suitable for mixed formats or strings with different lengths without additional handling.

DATE builds a date from numeric year, month, and day arguments; LEFT, MID, and RIGHT extract the relevant text pieces. The formula may normalize invalid components rather than flag every malformed string, so validate source data when accuracy matters. For a column containing several formats, use a deliberate import or cleanup process instead of applying one fixed-position formula to everything.

Understand Common Errors and Misleading Results

#VALUE!: Excel could not interpret the supplied text as a date. Check the locale, spelling of the month, separators, hidden characters, and whether the input contains a time or other text that the function cannot parse in that form.
A serial number appears instead of a date: The conversion may have worked. Format the result cell as a date to show it in a readable form.
The displayed date is wrong: Check for ambiguous day/month order and verify the source’s intended meaning. A successful conversion does not guarantee the interpretation is correct.
The formula itself is shown in the cell: The cell may be formatted as Text, or Show Formulas may be enabled. Set the cell to General, re-enter the formula, and check Formulas > Show Formulas.
#NAME?: Check that the function is spelled DATEVALUE and that text values inside a formula use straight double quotation marks. Also confirm that the formula was entered as a formula beginning with an equals sign.

If a formula result does not update after its source changes, check Formulas > Calculation Options > Automatic in Excel for Windows. Manual calculation mode can make results appear stale until recalculation. This setting affects workbook calculation generally; it does not change how DATEVALUE parses a date.

Choose a Reliable Conversion Method for Imported Data

For a small number of cells, a helper formula is transparent and easy to check. For a large imported list, choose a method that lets you specify the source format rather than relying on Excel to guess. Preserve a copy of the original data, especially when dates are ambiguous or used for reporting.

Text to Columns: In Windows Excel, select the text column and choose Data > Text to Columns. Follow the wizard and, where applicable, select Date and the order used by the source, such as DMY or MDY, before finishing. Check the results on a sample before replacing the source column.
Power Query: For repeatable imports, use Data > From Text/CSV, then set the date column’s type with the appropriate locale in Power Query. The exact controls vary by connector and Excel build. This approach is useful when a file is refreshed regularly and its date convention is known.
Formula parsing: Use DATEVALUE for recognizable date text or DATE with text extraction for a fixed layout. Keep the source text alongside converted results until the conversion has been validated.

Menu availability can vary between Windows, Mac, and Excel for the web, and some data-import features differ by platform or update channel. If the listed import control is not present, check the Data tab in that version and use a method it supports; do not assume a conversion has occurred merely because the cells look like dates.

Format, Sort, and Calculate After Conversion

Once Excel has a numeric date, you can format it without changing its stored value. Select the converted cells and choose a date display such as 14-Mar-2025 or 3/14/2025. A custom display can also show a four-digit year to make the result easier to inspect. Number formats affect appearance, not parsing or date order.

Real dates sort chronologically as numbers. Text dates may sort alphabetically, which can put dates in an order that looks inconsistent. A practical check is to sort a copy of the converted column oldest to newest and compare several entries with the source. If a formula is used in a helper column, copy the results and use Paste Special > Values only if you need fixed values rather than formulas; retain the originals until checks are complete.

Excel workbooks can use different date systems, most commonly the 1900 system and, in some workbooks, the 1904 system. This can affect the serial number shown for the same calendar date, particularly when copying values between workbooks. The displayed calendar date is generally the more useful check; avoid treating a serial number as a universal date identifier across workbooks.

Frequently Asked Questions

Why does DATEVALUE return #VALUE! for a date that looks valid?
It may be text in a format Excel does not recognize under the current regional settings, or it may include hidden spaces or nonprinting characters. Check the source type, confirm the date order, and try cleaning copied text before converting it.

Do I need DATEVALUE if Excel already recognizes the date?
No. If ISNUMBER returns TRUE for the source cell, Excel already stores it as a numeric date or date-time value. Apply a date format if you want a different display.

Why does DATEVALUE show a number instead of a date?
That number is the date serial value. Apply a date format to the result cell. The underlying serial is useful to Excel for sorting and calculations, even when you normally see a calendar date.

Can DATEVALUE convert dates written as YYYY-MM-DD?
It may, depending on Excel’s parsing rules and regional settings. If it does not, and every entry has that exact fixed layout, use DATE with the year, month, and day extracted from their positions, then verify the results.

Why did Excel change 04/05/2025 into the wrong date?
The text is ambiguous: it can represent April 5 or May 4. Excel interpreted it using an expected regional order. Confirm the source convention and import the column with an explicit date order or locale.

Does changing the cell format convert text into a date?
No. Formatting changes how a value is displayed, but it does not reliably convert date-like text. Convert the text first, then format the resulting numeric value as a date.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.