Quick Navigation
DATEVALUE Function
Summary
The DATEVALUE function converts text strings representing dates into Excel serial numbers that can be used in date calculations, sorting, filtering, and formatting. This is essential when working with imported data or text-formatted dates that Excel doesn't automatically recognize as dates.
Syntax
DATEVALUE(date_text)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| date_text | Text |
Yes | Text string or cell reference containing a date in Excel date format (e.g., "1/30/2008", "30-Jan-2008"). Year defaults to current year if omitted; time portion ignored. |
Using the DATEVALUE Function
DATEVALUE is perfect for converting imported or manually entered date text into proper Excel dates. Use it before date arithmetic, sorting, filtering, or when combining date components from multiple cells. Remember to format the result cell as a date to display properly.
Common DATEVALUE Examples
Basic Date Text Conversion
=DATEVALUE("8/22/2011")Converts "8/22/2011" text to serial number 40777 (August 22, 2011). Format cell as date to display properly.
Different Date Formats
=DATEVALUE("22-MAY-2011")Handles various formats like "22-MAY-2011" returning serial 40685.
Current Year Default
=DATEVALUE("5-JUL")Uses current year when omitted, returns serial for July 5th of current year.
Combining Cell Values
=DATEVALUE(A2&"/"&A3&"/"&A4)Combines day, month, year from cells A2:A4 into valid date serial number.
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: date_text not recognized as valid Excel date format or outside 1900-9999 range
Solution: Verify date format matches regional settings and falls within valid date range
Wrong date result
Cause: System date settings affect interpretation
Solution: Check Windows date format settings and use consistent format
Notes
- Excel stores dates as serial numbers starting January 1, 1900 = 1
- Apply date formatting after DATEVALUE to see dates properly
- Works with 1900 date system (default for Windows Excel)
- Common with imported CSV/text files containing dates
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+