Dates function
Excel DATEVALUE function
DATEVALUE converts a date written as text, such as "10/12/2026" or "19-Oct-2026", into the serial number Excel uses for dates. Use it on imported dates that sort as text and break date math, then format the result as a date to read it.
=DATEVALUE(date_text)Arguments
date_text- Text that looks like a date, in quotes or in a cell. Any time in the text is ignored.
DATEVALUE examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | |
|---|---|---|
| 1 | Imported (text) | Typed date |
| 2 | 2026-10-05 | Nov 2, 2026 |
| 3 | 10/12/2026 | |
| 4 | 19-Oct-2026 | |
| 5 | 2026-10-26 09:15 |
Download this sheet as an Excel practice file (.xlsx)
Result: 46300 shows as Oct 5, 2026 with the custom mmm d, yyyy format
Returns 46300, the serial number for October 5, 2026. Format the cell as a date to see it as one.
Result: 46314
Day-month-year text with a month name works too: 46314, which is October 19, 2026.
Result: 46321
The 09:15 is dropped and only the date comes back: 46321. Use TIMEVALUE for the time part.
Result: 7
Once both are real dates you can do date math: the two dates are 7 days apart.
Result: #VALUE!
B2 already holds a real date, a number, so DATEVALUE returns #VALUE!. It only accepts text.
How to use DATEVALUE
- Click the cell next to the text date.
- Type =DATEVALUE( and click the text cell.
- Press Enter. You see a number such as 46300.
- Format the cell as a date (Ctrl+1 > Date), then fill down.
Common DATEVALUE mistakes
#VALUE! on a cell that looks like a date
The cell already holds a real date, which is a number, and DATEVALUE only accepts text. Test it with =ISNUMBER(A2): TRUE means it needs no conversion.
Day and month swapped
DATEVALUE reads text in your computer’s regional date order. "05/10/2026" is May 10 on a US system and 5 October on a UK one. For text in the other order, build the date with DATE, LEFT, MID and RIGHT.
Seeing a number instead of a date
That is the date’s serial number, so the conversion worked. Apply a date format to the cell.
Text Excel cannot read
Text such as "5th Oct 2026" returns #VALUE!. Clean it first, for example with SUBSTITUTE, or use Data > Text to Columns.
DATEVALUE FAQ
How do I convert text to a date in Excel?
Use =DATEVALUE(A2) and format the result as a date. =VALUE(A2) also works, and Data > Text to Columns > Finish converts a whole column in place.
Why does DATEVALUE return #VALUE!?
Either the cell is already a real date, or the text is in a form Excel cannot read, such as the wrong day-month order for your region.
What is the difference between DATEVALUE and VALUE?
Both convert text dates. VALUE also converts text numbers and keeps any time as the decimal part; DATEVALUE returns only the whole date.
Does DATEVALUE work with dates without a year?
Yes. Text such as "5-Oct" uses the current year from your computer’s clock.
Related functions
Updated