How to convert text to a date in Excel
Formula (Excel and Google Sheets)
=DATEVALUE(A2)Use =DATEVALUE(A2). It reads text that looks like a date and returns a real date you can format and calculate with.
- Result on the example
- one result per row in
B2, filled down toB7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.
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.
=DATEVALUE(A2)Result3/5/2026 (46086)
A2"2026-03-05"The value in A2.
DATEVALUE(A2)3/5/2026 (46086)DATEVALUE: Converts date text to a date.- date_text
A2"2026-03-05"
- date_text
In Google Sheets
The same formula works in Google Sheets: =DATEVALUE(A2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =--A2 | 3/5/2026, 3/9/2026, 4/14/2026… | The double minus turns date-like text into its number, the same as DATEVALUE. |
| =VALUE(A2) | 3/5/2026, 3/9/2026, 4/14/2026… | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Convert text to a date
B2, convert the imported text in A2 to a real date, and fill down to B7.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.