Excel DATEVALUE function

Summary

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.
Syntax
				
					=DATEVALUE(date_text)
				
			
  • 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.

Examples

Simple DATEVALUE
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.
DATEVALUE with TODAY
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:

				
					=IF(ISNUMBER(A1), A1, DATEVALUE(A1))
				
			

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