# How to fix DATEVALUE returning #VALUE! in Excel

Pass real dates through and clean the text before converting: `=IF(ISNUMBER(A2),A2,DATEVALUE(TRIM(A2)))`.

**Excel:** `=IF(ISNUMBER(A2),A2,DATEVALUE(TRIM(A2)))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Date (mixed) | Clean date |
| 2026-03-05 |  |
| 2026-03-09 |  |
| Mar 12, 2026 |  |
| 2026-03-16 |  |
| 3/20/2026 |  |
| 2026-03-23 |  |

Formula in B2, filled to B7: `=IF(ISNUMBER(A2),A2,DATEVALUE(TRIM(A2)))`

Result: 3/5/2026; 3/9/2026; 3/12/2026; 3/16/2026; 3/20/2026; 3/23/2026

## How it works

DATEVALUE only accepts text. A cell that already holds a real date is a number, so DATEVALUE rejects it with #VALUE!, and text with stray spaces can fail too. ISNUMBER passes real dates through untouched, and TRIM cleans the text before DATEVALUE reads it.

## Other ways

- `=IFERROR(DATEVALUE(TRIM(A2)),A2)`: Tries the conversion and falls back to the cell itself when it is already a date.
- `=DATEVALUE(A2)`: The formula that fails: #VALUE! on the real dates. (Errors on the cells that are already dates.)

## Common mistakes

- Text in a different regional order, like "20/03/2026" on US settings, still returns #VALUE!. Rebuild it with DATE.
- Non-breaking spaces from web pages survive TRIM. Remove them first with `SUBSTITUTE(A2,CHAR(160),"")`.

## Questions

**How do I tell real dates from text dates?**

`=ISNUMBER(A2)` is TRUE for real dates. Text dates are usually left-aligned in the cell.

**Why does DATEVALUE give a five-digit number?**

That number is the date. Format the cell as a date.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/fix-datevalue-value-error