DATEVALUE
Converts a date stored as text into a valid Excel date serial number. The result can then be formatted as a date.
DATEVALUE(date_text)Arguments
date_textA text string representing a date in a recognized date format (e.g., "1/15/2026" or "15-Jan-2026").
=DATEVALUE("1/15/2026")Converts a text date into Excel's internal date number.
=DATEVALUE(A1)Converts a text date stored in cell A1 into a real date value.
=DATEVALUE("15-Jan-2026")+7Adds 7 days after converting the text into a valid date.
=IF(ISNUMBER(DATEVALUE(A1)),"Valid date","Invalid date")Checks whether A1 contains a recognizable date text.
- •The result is a number — format the cell as Date to display it properly.
- •DATEVALUE depends on your system's regional date settings (MM/DD/YYYY vs DD/MM/YYYY).
- •If the text cannot be recognized as a valid date, the function returns #VALUE!.
- •If the cell already contains a real date (not text), DATEVALUE is unnecessary.
- •For full control over year, month, and day, use the DATE function instead.
- •Applying DATEVALUE to a cell that's already a real date value, which returns a #VALUE! error since DATEVALUE expects text input, not an existing date
- •Not accounting for regional date format differences - text like "03/04/2026" can be interpreted as March 4th or April 3rd depending on the system's locale settings
- •Forgetting DATEVALUE strips any time component from the text, returning only the date serial number even if the original text included a time
DATEBuilds a date from separate year, month, and day numbers, rather than parsing a text string.TIMEVALUEDoes the same conversion job but for time portions of a text string instead of dates.TEXTThe reverse operation in spirit - converts a date value into formatted text instead of parsing text into a date.Why does DATEVALUE return a #VALUE! error?
The cell probably already contains a real date value, not text - DATEVALUE only works on text strings that look like dates. Applying it to an actual date causes the error.
Does DATEVALUE handle different date formats correctly?
It depends on your system's regional settings - a text string like "03/04/2026" can be read as different dates depending on whether the locale expects day-first or month-first format.
Does DATEVALUE keep the time if the text includes one?
No, it only returns the date serial number. Use TIMEVALUE separately if you also need to extract the time portion.
Need to translate a formula using DATEVALUE?
Use our translator to convert your complete formula