How to use Excel DATEVALUE Function

Syntax

DATEVALUE(date_text)

date_text: A date which is stored as a text and you want to convert that text into an actual date.

More Information on Excel DATEVALUE Function

  1. You can directly enter a date into the function by using double quotation mark.
  2. When you insert a textual date in the function it will simply give you a serial number. You can convert that serial number into a date by changing the format to date or by using the shortcut key : Control + Shift + #.

Examples

In the below example, I have inserted a date directly into the function by using double quotation marks.

If you skip adding these quotation marks it will return a #NAME? error in the result.

excel datevalue function to get a date serial number

In the below example, all the dates on the left side are in textual format.

excel datevalue function to get a date serial number
  1. A simple textual date which I have converted into a valid date.
  2. A date with all three components (Year, Month, or Day) in numbers.
  3. If there is no year in your textual date, it will take the current year as the year.
  4. And, if you have month name is in alphabets and no year.
  5. If you don’t have the day in your textual date it will take 1 as the day number.

Important Note: If you use a date which has no month in it, it will not convert that date into a date serial number.

Sample File

  • Plastic Cup

    Recently, a colleague came to me with an interesting problem she was having with dates. Anytime she entered a date in certain cells, Excel would default to the year 1931! I could see several cells with the date in long format that said November 17, 1931. After trying several attempts to enter other dates with the correct year, or clearing the cells of any formatting, formula precedents, etc. I asked her to enter the date while I watched. She typed 11/31 and pressed Enter, expecting to see 31-Nov. Excel, being clever that it is, interpreted 11/31 as November, 1931 and assumed the current year 20(17) to be the day as there is no such thing as 11/31/2017 in this or any year. The correction was to enter a legitimate date using the shorthand she previously used (11/30). Excel then overwrote the previous date format and all was well. It was strange that Excel would cling to that format like it did.