DATEVALUE Function
Turns a date written as text into a real date.
Category
Date & Time
Available in
All versions, Microsoft 365
Returns
A date (serial number)
Overview
Dates pasted from other systems often arrive as text — they sit on the left of the cell and won't sort or calculate. DATEVALUE reads that text and returns the date's serial number.
It understands the forms Excel recognises when you type: 3/15/2026, 2026-03-15, Mar 15, 2026 and 15-Mar-2026 (with US settings). Any time in the text is ignored.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| date_text | Yes | Text that looks like a date, or a cell holding it. |
Examples
Example 1 — Different text forms, same date
Every one of these returns 46096 — the serial number for March 15, 2026.
| A | B | |
|---|---|---|
| 1 | Text | DATEVALUE |
| 2 | "3/15/2026" | 46096 |
| 3 | "15-Mar-2026" | 46096 |
| 4 | "March 15, 2026" | 46096 |
| 5 | "2026-03-15" | 46096 |
Format the results as Short Date to see 3/15/2026.
Example 2 — Building a date from text pieces
A report lists the day, month name and year separately. Join them into text DATEVALUE can read.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Day | Month | Year | Date |
| 2 | 15 | Mar | 2026 | 3/15/2026 |
| 3 | 1 | September | 2026 | 9/1/2026 |
Common mistakes
- #VALUE! on a cell that already holds a date. DATEVALUE only accepts text. If the cell is right-aligned it's already a real date — use it directly.
- Day-first dates like 15/03/2026. With US settings Excel reads the first number as the month, so 15/03/2026 fails. Rebuild it with DATE, LEFT, MID and RIGHT, or use Data › Text to Columns › Date: DMY.
Notes
- To convert a whole column at once, Data › Text to Columns (Finish straight away) often fixes text dates without any formula.
- Related functions: DATE, TIMEVALUE, VALUE, TEXT.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.