# Why does DATEVALUE return #VALUE!?

DATEVALUE only converts text, and the cell already holds a real date. Check first: =IF(ISNUMBER(A2),A2,DATEVALUE(A2)).

**Excel:** `=IF(ISNUMBER(A2),A2,DATEVALUE(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-02 |  |
| 2026-03-09 |  |
| 2026-03-16 |  |
| Mar 23, 2026 |  |
| 3/30/2026 |  |
| 2026-04-06 |  |

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

Result: 3/2/2026; 3/9/2026; 3/16/2026; 3/23/2026; 3/30/2026; 4/6/2026

## How it works

DATEVALUE accepts text only. Rows 2, 4 and 7 already hold real dates, which are numbers, so DATEVALUE returns #VALUE! for them. ISNUMBER checks first: real dates pass through unchanged, and only the text is converted.

## Other ways

- `=--A2`: Shortest: arithmetic leaves real dates alone and converts date text.
- `=IFERROR(DATEVALUE(A2),A2)`: Tries the conversion and keeps the original when it fails. It also lets through text that isn't a date at all.

## Common mistakes

- Text that isn't a recognizable date still returns #VALUE!. Those cells need fixing by hand.
- Format the result column as a date, or real and converted dates both show as numbers.

## Questions

**Why does DATEVALUE work on some rows but not others?**

It converts text only. The rows that error already hold dates.

**How do I convert text that includes a time?**

`=--A2` keeps the time; DATEVALUE drops it.

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/datevalue-value-error