# How to convert text to a date in Excel

Use `=DATEVALUE(A2)`. It reads text that looks like a date and returns a real date you can format and calculate with.

**Excel:** `=DATEVALUE(A2)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Imported | Date |
| 2026-03-05 |  |
| March 9, 2026 |  |
| 14-Apr-2026 |  |
| 6/1/2026 |  |
| Jul 4 2026 |  |
| 2026/08/17 |  |

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

Result: 3/5/2026; 3/9/2026; 4/14/2026; 6/1/2026; 7/4/2026; 8/17/2026

## How it works

DATEVALUE reads text that looks like a date and returns the date number behind it. It understands the formats you could type into a cell: ISO dates like 2026-03-05, month names, and slash dates in your system's order. Format the result as a date to see it.

## Other ways

- `=--A2`: The double minus turns date-like text into its number, the same as DATEVALUE.
- `=VALUE(A2)`: Same result; VALUE also converts text times and numbers.

## Common mistakes

- Slash dates are read in your system's order: with US settings "6/1/2026" is June 1, not January 6. Rebuild day-first text with DATE.
- Leading or trailing spaces can cause #VALUE!. Use `DATEVALUE(TRIM(A2))`.
- If the cell already holds a real date, DATEVALUE returns #VALUE!. Check with ISNUMBER first.

## Questions

**How do I convert a whole column at once?**

In Excel 365, `=DATEVALUE(A2:A7)` spills down the column. In Google Sheets wrap it in ARRAYFORMULA.

**Why does the result show a number like 46086?**

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

**What if the text includes a time?**

DATEVALUE drops it. Use `=VALUE(A2)` to keep the date and the time, and format the result as a date and time.

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/convert-text-to-a-date