DATEVALUE function
DATEVALUE turns a date stored as text into a real date serial number you can sort and calculate with. It is useful after importing data where dates arrived as text.
Example
Read it as a sentence: turn the text 2024-01-31 into a date serial number, which gives 45322.
A
B
C
D
1
45322
2
3
Syntax
DATEVALUE(date_text)
-
date_text(required) - Text that Excel can read as a date, such as "2024-01-31".
Returns: A date serial number.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=DATEVALUE("1/9/2026") | 46031 | M/d/yyyy |
=DATEVALUE("2026-01-09") | 46031 | ISO text |
=DATEVALUE("9-Jan-2026") | 46031 | D-mmm-yyyy |
=DATEVALUE("January 9, 2026") | 46031 | Long month name |
=DATEVALUE("1/9/2026 10:30") | 46031 | Date with time, time dropped? |
=DATEVALUE("12/31/9999") | 2958465 | Last date |
=DATEVALUE("13/1/2026") | #VALUE! | D/m order in en-US |
=DATEVALUE("2/30/2026") | #VALUE! | Impossible date |
Watch out for
- Which text formats work depends on the PC's regional settings; ISO yyyy-mm-dd is the safest.
- Text with a time part returns only the date portion.
- Passing a cell that already holds a real date gives #VALUE!.
Where it works
- Excel
- DATEVALUE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .