DATEVALUE function
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.
- Available in
- Every current version
- Category
- Date functions
- 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.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Imported text | Result | |
| 3/15/2026 | 3/15/2026 | |
| 2026-04-01 | 4/1/2026 | |
| 4/30/2026 | 46 |
Practice DATEVALUE
Graded instantly, then re-checked on changed data.
Fix imported dates
B2:B6 came in as text. In C2, convert each one to a real date. Fill down to C6.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.
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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.