DATEVALUE

What does this 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.

Formatting

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.

Syntax

=DATEVALUE(date_text)

• date_text — required. Text that represents a date in a valid Excel date format

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.

Common date text formats

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”

Usage examples

=DATEVALUE("3/15/2025")

=DATEVALUE(A2)

=DATEVALUE("15-Mar-2025")

Features

• 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 applications

• 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 WEEKNUM.ISO, WORKDAY, DATEDIF, TODAY, SECOND, NOW, HOUR, NETWORKDAYS

This post is also available in RU.