Whenever we deal with a lot of data it becomes necessary to convert the text value into your preferred one. Also, you may need to convert the text value into the date and time. There are several processes for the conversion. we have tried to cover up the methods to do that. Go through the article to convert text to date and time in Excel. So, let’s get started.
Here, we will discuss some functions and other methods to convert text to date and time in Excel. For, different methods we may change the data set as per requirement. In some cases, we will show only the date, and in some cases both the date and time. We need to format the cells for this. Also, we need to add decimal values in the Value Column when working with time.
We will take some random date and time information to explain the methods.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Using DATEVALUE Function to Convert Text to Date and Time in Excel
The DATEVALUE function converts a date in the text format to a number in Excel.
Syntax
=DATEVALUE(date_text)
So, the formula to convert a text value to date. For example, =DATEVALUE(B5), where B5 is a cell with a date stored as a text string.
From our data in the text column, we have the date in text format and we will convert this Excel date format.
Steps:
- Firstly, go to cell C5Â and insert the below formula.
=DATEVALUE(B5)- Eventually, press ENTER and pull down the Fill Handle till the last.
- In the value column, we only have the numeric value. But we want the date values here. To format cells according to date press CTRL + 1 key.
- Momentarily, the Format Cells dialog window appears. choose Date from the Category and select a date format like the image below.
- So, go to the Date Column and in this column, we get the corresponding dates. This Date Column is formatted as mentioned already. On cell D5 write the formula:
=C5- Consequently, press ENTER and drag it down to the last.
So, we get the dates with the date format from the text.
Note:
In row 8 of the value and date column, we do not get any value as the DATEVALUE function cannot convert any numeric value.
Read More: How to Convert Text to Date in Excel
2. Applying Excel VALUE Function to Change Text to Date and Time
The VALUE function converts a text string that represents a number to a number.
Syntax
=VALUE(text)
We can convert text to both date and time using this function.
Steps:
- Initially, add time to our data. We added time in cells B5 and B6.
- Eventually, move to cell C5 of the Value column and enter the following formula.
=VALUE(B5)Here, we can see that the VALUE function can convert any value to a number. So, we get the date and time against all the inputs.
- Consequently, press ENTER and drag it down to the end.
- Apparently, we get decimal values in the corresponding cells where time exists.
- At this moment, move to cell D5 to get the date and time by inputting the formula:
=C5- Subsequently, press ENTERÂ and drag it down for other cells.
Read More: How to Convert Number to Date in Excel
3. Combining SUBSTITUTE and VALUE Functions to Convert Text to Date in Excel
The SUBSTITUTE function changes the existing text with a new one.
Syntax
=SUBSTITUTE(text, old_text,new_text,[instance_num])
Arguments
text: is the reference text or reference of cell.
old_text: This text has been replaced.
new_text: this text may be in the previous text position.
instance_num:Â This will specify the instance of old_text, that will be substituted with new_text. When specifying instance_num, that time the mentioned instance of old_text will be substituted. Otherwise, all occurrences of old_text will be replaced with the new_text.
In this article, we will use the SUBSTITUTE function with the VALUE function. Sometimes VALUE function cannot convert the text string exactly. In that case, we will use the SUBSTITUTE function to remove the string that cannot be converted by the VALUE function.
Steps:
- Firstly, modify the data in the Text column using the decimal sign.
- Now, on cell C5 write the formula. Here, we will replace Dot (.) with Forward slash (/). So, the formula becomes:
=VALUE(SUBSTITUTE(B5,".","/"))- Moreover, press ENTERÂ and drag it down.
- Apparently, on cell D5 write the following formula to get the Date.
=C5
Read More: How to Convert General Format to Date in Excel
4. Converting Text to Date and Time with Mathematical Operators
At this moment, we may insert various mathematical interpretations. In other words, we can do addition, subtraction, multiplication, and can also use dividing operators here.
Steps:
- Firstly, move to the C5 cell and select the reference to the B5 cell.
- Apparently, insert an Addition (+) sign and input a zero (0) after it. Thereby, the final representation will be:
=B5+0- Sequentially, press ENTER.
- Now, in cell D5, write:
=C5- Then, press ENTER.
So, get the date & time from a text by using mathematical operators. Other operators will be used on the rest of the cells.
- Now, apply Multiplication (*), Division (/), Exact (–), and Minus (-) operators respectively on the cells C6, C7, C8, and C9. And we get the below result.
Finally, drag down the Fill Handle tool for getting the following output rest of the cells.
Read More: How to Convert 8 Digit Number to Date in Excel
5. Using Excel Find and Replace Option to Convert Text to Date and Time
In this section, we will use the Find and Replace option to remove any text by others and will get our desired result. First, we will modify the data to apply this method and the data will look like this:
Steps:
- Firstly, copy the data from the Text column to the Formatting column.
- Apparently, Select the range B5:B9 and type CTRL + H.
- Consequently, the Find and Replace window pops out. Replace Dot(.) with Forward Slash (/).
- Lastly, press Replace All.
- However, move to cell D5 and refer C5 here. Drag it down to the last cell to get the following output.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
That’s all about today’s session. And these are some easy methods to convert text to date and time in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.
