Usually, Excel is capable enough to identify your data in the cells. For instance, the cells containing text will be identified as text, dates as dates, and numbers as numbers. However, it does not happen always, especially when your data is copied from somewhere else.
Typically, numbers are used in dates that’s why Excel can handle it pretty well. On the other hand, sometimes dates are formatted as text or it might be possible that your dates are not in the proper format that’s why Excel does not consider them as dates. So, what would you do in this case?
In the backend Excel, stores dates as numbers, though you can format dates in multiple ways to use them in different datasets. And if the dates are stored as text in Excel, you will not be able to identify dates from the data.
What does it mean to Convert Text to Date in Excel?
Most professionals tend to import data in Excel from other tools and sometimes data contains dates. With Excel, you can easily identify dates only when dates are in the correct format. You need to convert the data from text to date so that Excel can recognize it as a date.
How You can Identify Normal Excel Dates from “Text Dates”
Whenever you import data in Excel, you may have to face issues with date formatting. You would find that imported data looks like typical Excel dates; however, in the backend, they are not stored as dates. In Excel, these dates are formatted as text, which means that you cannot organize a table with dates properly. Moreover, you cannot use those text dates in formulas, charts, Pivot Tables, or other Excel features that identify dates.
Below are some useful signs that let you determine if these are dates or text values.
Dates Vs Text Values
- Dates are always right aligned automatically.
- Whereas Text values are left-aligned by default.
- You will find the Date format in the Number Format menu given on the Home tab.
- On the other hand, Text values are found in General format in the Number Format menu given on the Home tab.
- You will see the Average, Count, and SUM options on the Status Bar when the dates are selected.
- In opposition, you will see only “Count” if several text dates are selected on the Status Var.
How to Covert Text to Date in Excel using the VALUE Function?
The VALUE function is used in most cases when your data contains a date but Excel does not consider it as a date. To convert this text date into proper date format, you can apply the VALUE function. This function converts the text value into a five-digit Unix timestamp that changes into a proper date format so that Excel can identify it as what it is.
The text string can be converted simply by using the VALUE function and for this process, you need to type =VALUE(A2).
In this syntax, cell A2 has the text value that needed to be covert. Below we have an example in which you can see data contains the day-month-year date format.
It works only for specific kinds of date values. You will also notice that dates with decimal places are not supported values such as 12.05.1990. In this case, you will have to face a #VALUE error for the cells that are unable to convert the values.
Unix timestamp is the one thing that can convert this value in Excel cell formatting. For this, you need to select the date cells and then choose the Date option given in the Number drop-down menu.
How to Convert Text to Date in Excel using DATEVALUE Function?
Using the DATEVALUE function allows Excel users to change a text date in the serial number so that Excel can easily recognize it as a date.
Below is the syntax you can use for it:
DATEVALUE(date_text)
Considering the above syntax, the formula to change a text value into a date is =DATEVALUE(A1), in which A1 is considered as a cell containing a date stored as a text string.
As you know the DATEVALUE function will convert the text date value in the serial number, you will have to change the format so that it apparently looks like a date. Below you can see the DATEVALUE formula in a functional state.
Things to Consider
While using the DATEVALUE function in terms of converting text value to a date format, you have to consider a few things:
Keep in mind that time data in text value would be ignored. You can use the VALUE function when you have to convert the text values containing date as well as time.
When the year part is not included in the text date, the DATEVALUE function adds the current year by default from your system’s clock.
You may find a #VALUE error when using the DATEVALUE function on dates earlier than January 1, 1900.
Using the DATEVALUE function will not change a number to date and it does not even process the text value that appears as a number. For this, you can use the VALUE function used in Excel.
