What is the DATEVALUE Function?
The DATEVALUE function in Excel converts a text-formatted date into a serial number. Excel can then read the serial number to understand the date. This Excel function is necessary when a cell contains date information but it’s stored in regular text. Instead of automatically converting it to a date, Excel sees the cell as just numbers and letters, making it hard to work with. This can happen if the date has been copied or imported from elsewhere. You can use the DATEVALUE Excel function to produce the serial number of the date which can then be used to properly format it as a date and use it with other date-based formulas, sort it with other dates, etc.
DATEVALUE Function Syntax & Arguments
All formulas that use this function should be formatted like this: =DATEVALUE(date_text) date_text is the only argument it supports. It can reference other cells or the date information can be stored within the formula. Here are some rules to remember about the DATEVALUE function:
If the date_text year is omitted, the current year is used.If date_text includes time information, Excel ignores it.If the date information is entered directly into the formula, it must be surrounded by quotes.If the date information is referenced in another cell that includes the text name of the month (e.g., Mar or March), the month must be in the second position (like 31-Mar-2020).The #VALUE! error will display if date_text falls outside of the date range 1/1/1900–12/31/9999.The #VALUE! error will display if date_text appears to be a number (i.e., it doesn’t have dashes or slashes like a typical date).
DATEVALUE Function Examples
Here’s a look at some of the different ways you might use this function:
Reference Date From Another Cell
Assuming A1 reads as 4-4-2002, this DATEVALUE formula example would produce the serial number 37350.
Enter Date Inside Formula
This is another way to use this function. Entering the date in quotes is a substitute for calling on another cell. This formula produces the serial date 39441.
Make Date From Multiple Cells
In this example of the DATEVALUE function, the same setup is used but we’re grabbing the date information from three separate cells: A2=5, A3=18, and A4=2017. This requires the ampersand sign so that we can add slashes to separate the day, month, and year. However, the result is still a serial number since that’s what the function is for, so we’d have to format the cell as a real date (see below) to see it presented as 5/18/2017.
Use Ampersand in Date Formula
In this example, the function is a lot like the one above it, but instead of using cell references to figure the day and year, we’re entering those in manually using double quotes.
Extract Date From Cell With Other Data
If the cell contains other information that you don’t need, you can use functions like LEFT and RIGHT to isolate the date. In this example, the DATEVALUE function is coupled with the LEFT function so that it looks at only the first 10 characters from the left. The result is 41654, which Excel can format as a date to produce 1/15/2014.
Extract Date With MID Function
Finally, we have this formula that combines not only the MID function but also the FIND function to extract the date and present it in the serial number format. The MID function sets A2 as the target and uses FIND to define the space (" “) as the point where the function should start counting. The number at the end of the MID function defines how many characters to extract, which is 7 in our example. The result is 43944, which when formatted as a date turns into 4/23/2020.
DATEVALUE Errors
Below are some examples of situations where the DATEVALUE function will show an error. Per the rules mentioned above, the rows with the #VALUE! error contain data that can’t be processed by this function.
Formatting Numbers Into Dates
When Excel produces a date’s serial number, you’re left with a number that signifies how many days away it is from 1/1/1900. This is hardly usable, so what you need to do is format that cell as a regular date.
