Excel DATEVALUE – A Step-by-Step Guide

(Note: This guide on how to use the Excel DATEVALUE function is suitable for all Excel versions including Office 365)

Imagine this: you worked hard on an important spreadsheet report and imported a lot of data from a PDF or Word document. In this document, the dates are formatted as text. When you import this into Excel, all these data are still retained as text.  

How do you fix this mess? 

If you are wondering if this is even possible to fix in the first place, don’t worry. Fortunately, Excel has functions that can help you clean the data. 

In this guide, we are going to explore the DATEVALUE function and learn how to use it to clean date formats in Excel. 

You’ll learn:

• What is the DATEVALUE Function?
• Excel DATEVALUE Syntax
• Why Doesn’t DATEVALUE Work Sometimes?
• DATEVALUE Alternative

Related:

How to Use the ROUND Function in Excel?

The Excel SMALL Function – 3 Best Examples

How to Use Excel COUNTIFS: The Best Guide

What Is the DATEVALUE Function?

We use various date formats across the globe. But software like Excel can understand and process only certain date formats. 

The DATEVALUE formula converts any user-friendly date format into Excel’s default date format automatically. This means you don’t have to worry about entering the right date format every time. 

All you have to do is use the DATEVALUE function just once and convert all these random dates into proper Excel date formats in one go. 

This will save you a lot of time and energy. 

Note: All dates in Excel are stored as serial numbers starting with 1 for 1st Jan 1900. 

This means 2nd Feb 1900 is stored as 33 and 13th April 2022 is stored 44664. 

So, for example, DATEVALUE(“23 Sep 2022”) will automatically convert to 44827. 

Remember to include the text inside the double quotes (“”) 

You can also use cell references directly inside the DATEVALUE function. If that is the case, double quotes are not needed. 

Next, you can once again convert this into a proper date format using the Number toolbar in the Home tab. 

Excel DATEVALUE Syntax

As discussed the DATEVALUE function has a very simple syntax. 

DATEVALUE(Text)

Here the Text should be either a direct value or a cell reference of a valid Excel date written in text format. 

Why Doesn’t DATEVALUE Work Sometimes?

Please note that the DATEVALUE function doesn’t work when your dates are already in the date format. It will return a #VALUE error instead. 

Also, the text you are trying to convert should be typed in the Excel date format. 

Ex: “2/3/2022”, “2-March-2022”, “2-3-2022”, and “23 Sep 2022” are recognized Excel date formats.

On the other hand, texts like “March 7, 2022” are not recognized by Excel. 

Hence, they cannot be used for the DATEVALUE function. 

Note: The DATEVALUE formula will return a #VALUE error if the date you are trying to convert lies beyond the recognized range of 1 January 1900 to 31 December 9999. 

Also Read:

Battle of the Excel Lookup Functions

How to Use the Excel IFS Function? – 2 Easy Examples

How to Use the TEXTJOIN Excel Function? 3 Easy Examples

DATEVALUE Alternative

Sometimes, the DATEVALUE can be very frustrating to use, especially because of its inability to process real dates. Hence, a better alternative would be to simply add a zero ‘0’ to the original text date. 

What it essentially does, is that it simply forces Excel to convert text into a number. If Excel detects a valid date format in a text form, it simply converts it into a date. 

On the other hand, if it finds a normal date, it simply ignores it without causing any errors. 

So, in this example, if you want to convert the range A2:A8 to a date. Just type A2+0 and drag it till the end of the range to see the magic happen. 

Suggested Reads:

The FORMULATEXT Excel Function – 2 Best Examples

The Excel CHOOSE Function – 4 Best Uses

How to Apply the Accounting Number Format in Excel? (3 Best methods)

Closing Thoughts

The Excel DATEVALUE function is a nifty little feature and most people are not aware of it. 

But it is a lifesaver, especially when it comes to cleaning dates and formatting them properly. 

Do remember to use this function the next time you are stuck with dates in text format. I also recommend that you practice this in a sample worksheet to gain a first-hand experience of what works and what doesn’t.

If you find this guide helpful and are looking for more guides, please visit our Excel resources centre. 

Want to skill up in Excel?

Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.