# DATEVALUE function in Excel

> DATEVALUE converts a date that is stored as text into a real date that Excel can sort, subtract and format.

Source: https://spreadsheetpractice.com/functions/datevalue · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** DATEVALUE converts a date that is stored as text into a real date that Excel can sort, subtract and format.
- **Syntax:** `=DATEVALUE(date_text)`
- **Most common use:** `=DATEVALUE(A2)` returns 3/15/2026 (Text from an export becomes a real date)
- **Available in:** Every current version
- **Category:** [Date functions](https://spreadsheetpractice.com/functions/category/date)
- **Returns:** A date serial number. Format the cell as a date to read it.
- **Google Sheets:** Works in Google Sheets, though the text formats it accepts depend on the locale.

## What it does

DATEVALUE converts a date that is stored as text into a real date that Excel can sort, subtract and format. You need it when an export or another system hands you dates as text, which sort alphabetically and break date functions. Which text formats it understands depends on your system's date settings.

## Syntax

`=DATEVALUE(date_text)`

| Argument | Required? | What it is |
|---|---|---|
| `date_text` | Required | Text that looks like a date, such as "3/15/2026" or "2026-03-15", or a cell holding it. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | `=DATEVALUE(A2)` | 3/15/2026 | Text from an export becomes a real date |
| C3 | `=DATEVALUE(A3)` | 4/1/2026 | Year-month-day text converts too |
| C4 | `=DATEVALUE(A4)-DATEVALUE(A2)` | 46 | Real dates subtract: 46 days apart |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C |
|---|---|---|---|
| 1 | Imported text |  | Result |
| 2 | 3/15/2026 |  | 3/15/2026 |
| 3 | 2026-04-01 |  | 4/1/2026 |
| 4 | 4/30/2026 |  | 46 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | The text doesn't match a date format your system recognizes, often because it uses day/month order on a month/day system. Rebuild it with DATE and LEFT, MID and RIGHT. |
| #VALUE! on a cell that is already a date | DATEVALUE only accepts text. If the cell already holds a real date, use it directly. |
| A five-digit number instead of a date | The conversion worked. Format the cell as a date. |

## Tips

- Text dates sit on the left of the cell by default and real dates on the right, which is a quick way to spot them.
- For a whole column, Data > Text to Columns with the Date option converts text dates in place without formulas.

## Related functions

- [DATE](https://spreadsheetpractice.com/functions/date): DATE builds a real date from a year, month and day.
- [VALUE](https://spreadsheetpractice.com/functions/value): VALUE converts text that looks like a number into a real number Excel can add, average and compare.
- [TEXT](https://spreadsheetpractice.com/functions/text): TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026".

## Questions

### How do I convert text to a date in Excel?

Use =DATEVALUE(A2) and format the result as a date. If DATEVALUE can't read the format, split the text into parts and use DATE.

### Why does DATEVALUE return a number?

Dates in Excel are numbers. DATEVALUE returns the serial number; apply a date format to display it.

### How can I tell if a date is stored as text?

Text dates are left-aligned by default, and =ISTEXT(A2) returns TRUE for them.

## Practice

A graded DATEVALUE exercise ("Fix imported dates") is on [the DATEVALUE page](https://spreadsheetpractice.com/functions/datevalue). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
