how to use excel datevalue functionUse Excel DATEVALUE Function when you have dates which are stored in text. DATEVALUE Function will help you to convert that textual dates into actual dates.

Excel stores 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 having a serial number.

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. You can use DATEVALUE Function.

Go ahead and learn this function.

Download this Quick PDF Guide to learn about Excel DATEVALUE Function.

Syntax for Excel DATEVALUE Function


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

More Information on Excel DATEVALUE Function

  • You can directly enter a date into the function by using double quotation mark.
  • When you insert this a textual date in this function it will simply give you a serial number, you can convert that serial number into a date by changing format to date or by using the shortcut key. Control + Shift + #. [More Shortcuts]

Examples to Understand

In 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 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) is in numbers.
  3. If there is no year in your textual date it will take the current year as the year.
  4. Same in case, if you have month name is in alphabets.
  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.

Learn more about dealing with textual dates from here.

Using Excel DATEVALUE Function is really helpful when you have a large set of textual dates. You can quickly convert then into actual dates.

DATEVALUE is one of the most useful data cleansing functions.

Download this sample file from here and try it yourself. And, Please don’t forget to download my Excel Productivity Guide. Worth $20, Absolutely Free.

What is Next?

Do you want to learn more excel function for data cleansing? Here it is.


Or try some of these daily use functions.