| Function category | Date and time functions |
| Volatility | Non-volatile |
| Similar functions | TIMEVALUE, VALUE, TEXT |
What does the DATEVALUE function do?
This function is used to convert text into a date that can be used in calculations.
Dates expressed as text are often created when data is imported from other programs.
DATEVALUE: result and input format
The result typically displays as a number representing the date. This number can be formatted into any standard date format using the "Format Cells" procedure.
DATEVALUE function syntax
=DATEVALUE(date_text)- date_text — required. Text that represents a date in a valid Excel date format
DATEVALUE formula example
The example uses DATEVALUE and TODAY functions to calculate the number of days remaining until a product's expiration date.
The DATEVALUE function was used because the date was entered into the cell as text, likely after importing from an external program.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Expires (text) | Days left on 10/22/2026 |
| 2 | Milk | 10/25/2026 | 3=DATEVALUE(B2)-TODAY() |
| 3 | Cheese | 12/01/2026 | 40=DATEVALUE(B3)-TODAY() |
| 4 | Yogurt | 10/20/2026 | -2=DATEVALUE(B4)-TODAY() |
The dates in column B are text, so DATEVALUE turns them into dates before subtracting TODAY(). A negative number means the product has already expired. Format column C as General, or Excel may show the result as a date.
Date text formats DATEVALUE recognizes
DATEVALUE recognizes these common text date formats:
- "March 15, 2025" or "15-Mar-2025"
- "3/15/2025" or "03/15/2025"
- "2025-03-15" (ISO format)
- "15 March 2025"
DATEVALUE formula examples
=DATEVALUE("3/15/2025")=DATEVALUE(A2)=DATEVALUE("15-Mar-2025")| A | B | C | |
|---|---|---|---|
| 1 | Text | DATEVALUE | As a date |
| 2 | 3/15/2025 | 45731=DATEVALUE(A2) | 03/15/2025=B2 |
| 3 | 03/15/2025 | 45731=DATEVALUE(A3) | 03/15/2025=B3 |
| 4 | 15-Mar-2025 | 45731=DATEVALUE(A4) | 03/15/2025=B4 |
| 5 | March 15, 2025 | 45731=DATEVALUE(A5) | 03/15/2025=B5 |
| 6 | 2025-03-15 | 45731=DATEVALUE(A6) | 03/15/2025=B6 |
| 7 | 15 March 2025 | 45731=DATEVALUE(A7) | 03/15/2025=B7 |
| 8 | 3/15/2025 14:30 | 45731=DATEVALUE(A8) | 03/15/2025=B8 |
| 9 | 15.03.2025 | #VALUE!=DATEVALUE(A9) | #VALUE!=B9 |
All recognized formats give the same serial number 45731 — format the cell as a date to see it. The time part is ignored. “15.03.2025” returns #VALUE! in US-English Excel: day.month.year isn’t a US date format (it works in European locales).
DATEVALUE features and limitations
- Converts text dates to Excel serial numbers (number of days since January 1, 1900)
- Returns #VALUE! error if text cannot be recognized as a date
- Time information in text is ignored (use TIMEVALUE for time conversion)
- Essential for working with dates imported as text from CSV files, databases, or other systems
- Result can be formatted as any standard date format
Practical examples of DATEVALUE
- Converting imported data from text to usable dates
- Calculating days between dates when one is stored as text
- Data cleanup and standardization
- Integration with other systems that export dates as text
- Automating date calculations with mixed data sources
Other Date and Time functions in Excel
TIMEVALUE, TIME, YEAR, DATE, EDATE, DAY, WEEKDAY, YEARFRAC, EOMONTH, MONTH, MINUTE, WEEKNUM and ISOWEEKNUM, WORKDAY, DATEDIF, TODAY, SECOND, NOW, HOUR, NETWORKDAYS
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.