Excel DATEVALUE Function – An Overview
When to use Excel DATEVALUE Function
Excel DATEVALUE function is best suited for situations when a date is stored as text. This function converts the date from text format to a serial number that Excel recognizes as a date.
What it Returns
It returns a serial number for a date. For example, if we use the function DATEVALUE(“1/1/2016”), it would return 42370 which is the serial number for 01-01-2016.
- date_text: this argument could be a date in text format (entered manually) or a cell reference that contains the date in text format.
- If date_text is manually entered in the DATEVALUE function, it should be in double quotes.
- If date_text is a cell reference, it should be in text format.
- DATEVALUE function returns a serial number.
- For example: =DATEVALUE(“1/1/2014”) returns 41640 (which is the serial number for this date).
- If you do not use the day argument, and only use the month and year arguments, it returns the serial number for the first day of that month.
- For example, DATEVALUE(“1/2014”) would return the serial number 41640 (which represents the first day of the first month of 2014).
- If the Year argument is not used, excel automatically takes the current year (based on computer systems clock settings).
- The serial number returned by the DATEVALUE function can vary depending on the computer’s system date settings.
- For example, 01/03/2016 would be January 3rd in the US and 1st March in the UK.
Excel DATEVALUE Function – Examples
Here are examples of using the Excel DATEVALUE function.
Example 1: Getting the Serial Number of a Date Entered as Text
In the above example, the DATEVALUE function returns the serial number of the date entered within quotes. The date entered needs to be in a format that is recognized by Excel as a valid date format (else it returns a #VALUE! error.
Note that the value returned by the DATEVALUE function depends on your system’s clock setting. If it DD-MM-YYYY then “01-06-2016” would mean June 1, 2016, but for MM-DD-YYYY it would be January 6, 2016.
Example 2: Using Cell References Where Dates are Entered as Text
When dates are entered as text in a cell, you can use the DATEVALUE function to get the serial number of the date it represents. Note that the date needs to be in a format that is recognized by Excel as a valid date format.
In the above example, the DATEVALUE function converts 1-6-16 and 1-June-16 to its date value.
Excel DATEVALUE function can be useful when you have dates in a text format that you want to filter, sort, or format as dates, or use in date calculations. In the text format, you won’t be able to sort the dates as Excel would consider it as text and not numbers.
Example 3: When the Year is Not Specified
When the Year is not specified, DATEVALUE uses the current year to return the date value (current year is 2016 in this case).
Example 4: When the Day is Not Specified
When the Day is not specified, DATEVALUE uses the first day of the month to return the date value.
In the examples above, when 6-16 is used in the DATEVALUE function, it returns 42522, which is the date value for 1 June 2016.
Note: If the ‘year’ value is greater than 12, then the DATEVALUE function would consider it as a case of ‘missing month’ and use the first day of the specified month. But if the year is less than or equal to 12 (let’s say 2011 or 2012), then the DATEVALUE function would consider it as a case of the missing year, and use the current year to return the date value.