How to use Excel DATEVALUE Function

Quick Intro

You can use DATEVALUE Function when you have dates which are stored as a text. It will help you to convert those textual dates into actual dates.

As you know, excel stores a date as a serial number. But, when a date is stored as text, even the format is correct, you can’t use that date in any kind of calculation.

In that situation, you can use DATEVALUE Function to convert that text into an actual date.

If you want to check whether a date is valid or not. Try to convert that date into a number. And, if you are not able to convert that date into a serial number that means it’s a 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


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 Convert A Textual Date Into Valid Date

  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 & 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

download sample file to learn more about this tips

What’s Next?

To learn more about Excel DATEVALUE Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There is also you have DATE  function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.