The DATEVALUE function
Converts a date written as text, such as "2024-03-15", into a date serial number Excel can calculate with and format.
Syntax
DATEVALUE(date_text)- date_text
- A date as text, in a format your Excel reads as a date, or a cell that holds one.
Examples
B1=DATEVALUE("2024-03-15")
| A | B | |
|---|---|---|
| 1 | 45366 |
B1=DATEVALUE("2024-03-15")-DATEVALUE("2024-01-01")
| A | B | |
|---|---|---|
| 1 | 74 |
B1=TEXT(DATEVALUE("March 15, 2024"),"yyyy-mm-dd")
| A | B | |
|---|---|---|
| 1 | 2024-03-15 |
Questions
Why does DATEVALUE return
Excel does not recognize the text as a date. Dates in a format from another country, such as 15/03/2024 on a US computer, are the usual cause. Rebuild them with DATE, LEFT, MID and RIGHT.
Why is the result a number like 45366?
That is how Excel stores dates, counting days from Jan 1, 1900. Format the cell as a date to see Mar 15, 2024.