Excel DATEVALUE function


The Excel DATEVALUE function is designed to convert a date that’s formatted as text into a serial number that Excel recognizes as a valid date. This conversion is crucial when dealing with dates that are input as text and need to be used in calculations, sorting, or any other date-specific functionality in Excel.
  • date_text: The date in text format that you want to convert to a serial number.
Return value
A serial number representing a valid Excel date.

How to use

Use DATEVALUE when you have a date represented as text and you need to convert it to a format that Excel can recognize and work with as a date. The function takes a single argument, the date_text, which should be a date in a recognized text format. If the date_text is in a cell, ensure it’s stored as text. If you’re typing it directly into the formula, it must be enclosed in quotes.


Converting a Text Date to Serial Number: To convert a text date into a serial number:
					=DATEVALUE("July 4, 2020")
This formula takes the text “July 4, 2020” and converts it to the corresponding serial number that Excel recognizes as July 4, 2020.
Calculating Days Since a Date: To calculate how many days have passed since a specific date entered as text:
					=TODAY() - DATEVALUE("1/1/2020")
This formula converts the text “1/1/2020” to a serial number and subtracts it from today’s date, returning the number of days since January 1, 2020.
DATEVALUE for Error Handling

Handling Dates in Mixed Format: To convert a date that may already be in serial number format or as text:


This formula checks if the value in A1 is already a number (a valid Excel date). If so, it uses it directly; otherwise, it tries to convert it using DATEVALUE.

DATEVALUE with Dynamic Input

Dynamic Date Conversion for Reporting: To convert a series of text dates for analysis:

					=DATEVALUE(B1 & "/2020")
Assuming B1 contains a day and month like “7/4”, this formula appends “/2020” to it and converts the resulting “7/4/2020” into a serial date number for the year 2020.

Additional Notes

  • DATEVALUE is especially helpful when importing data from other systems where dates might come in as text.
  • The function requires the date to be in a format recognizable by Excel. If it can’t interpret the date_text, it will return a #VALUE! error.
  • Keep in mind that DATEVALUE returns a serial number. To display it as a human-readable date, you’ll need to apply a date format to the cell.
  • In scenarios where your data might be a mix of valid and text dates, consider using conditional functions or error handling to prevent conversion errors.

Related Functions

Excel VALUE function

The Excel VALUE function converts a text string that represents a number into a numeric value, crucial for calculations.

Excel TIMEVALUE function

The Excel TIMEVALUE function converts a time in text format to a serial number, enabling time calculations and comparisons.

Excel NUMBERVALUE function

The Excel NUMBERVALUE function converts text to a number in a locale-independent manner, essential for processing textual numbers.

Content Navigation