# Convert Text to Date and Time in Excel (5 Methods)

In Excel, we mainly work with data. We organize and manipulate data as per our requirements. We find out the required information from our managed data. But, in this article, we will discuss how to convert text to date and time in Excel. Most of the time when we copy any data that contains date and time information changes to text format. Then it becomes difficult for Excel to detect which is data and time information. And we need to convert that text data into date and time format.

We will take some random date and time information to explain the methods. ## 5 Methods to Convert Text to Date and Time in Excel

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

For both Date and Time, we need to set the format this way, Only For Date, we need to set the format this way, ### 1. Use DATEVALUE Function 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 as text format we will convert this Excel date format.

Step 1:

• Go to Cell C5.
• Write the DATEVALUE function.
• Select B5 as the argument. So, the formula will be:
`=DATEVALUE(B5)` Step 2:

• Now, press Enter. Step 3:

• Pull the Fill Handle till last. Step 4:

• In the value column we only the numeric value. But we want the date values here.
• So, go to the Date Column and in this column, we get corresponding dates. This Date Column is formatted as mentioned already. On Cell D5 write the formula:
`=C5` Step 5:

• Now, press Enter.
• Pull the Fill Handel till 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. Insert Excel VALUE Function to Convert Text to Date and Time

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.

Step 1:

• 1st add time with our data. We added time in Cell B5 and B6. Step 2:

• Go to Cell C5 of Value Column.
• Write the VALUE function.
• Use B5 in the argument section. So, the formula is:
`=VALUE(B5)` Step 3:

• Now, press Enter.
• Drag the Fill Handle to Cell B9. Step 4:

• We get decimal values in the corresponding cells where time exists.
• Now, go to Cell D5 to get the date and time by inputting the formula:
`=C5` Step 5:

• Then press Enter.
• And drag the Fill Handle to the last. 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.

### 3. Combine SUBSTITUTE and VALUE Functions to Convert Text to Date in Excel

SUBSTITUTE function replaces existing text with a new text in the existing text string.

Syntax

=SUBSTITUTE(text, old_text,new_text,[intance_num])

Arguments

text – is the reference text or cell reference.

old_text – This text will be replaced.

new_text – this text will be on 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.

Step 1:

• Modify the data in the Text Column using the decimal sign. Step 2:

• Now, on Cell C5 write the formula. Here, we will replace Dot (.) with Forward slash (/). So, the formula becomes:
`=VALUE(SUBSTITUTE(B5,".","/"))` Step 3:

• Press the Enter button.
• And drag the Fill Handle option to the last cell to get values for all cells. Step 4:

• On Cell D5 write the following formula to get Date.
`=C5` Step 5:

• Get values for the rest of the cells by pulling the Fill Handle option. ### 4. Use of Mathematical Operators to Convert Text to Date and Time

In this section, we will different mathematical operators to convert text to date and time. We will use plus, minus, multiplication, division operators here.

Step 1:

• Go to Cell C5.
• Refer to Cell B5 here.
• Now, put a Plus (+) sign and add 0 with this. So, the formula becomes:
`=B5+0` Step 2:

• Now press Enter. Step 3:

• 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.

Step 4:

• Now, apply Multiplication (*), Division (/), Exact (–), and Minus (-) operators respectively on the cells C6, C7, C8, and C9. And we get the below result. Step 5:

• Now, drag the Fill Handle icon to Cell D9. ### 5. Convert Text to Date Using Find and Replace Option in Excel

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: Step 1:

• Now, copy the data from the Text Column to the Formatting Column. Step 2:

• Select the range B5:B9.
• Then type Ctrl+H. Step 3:

• Replace Dot(.) by Forward Slash (/). Step 4:

• Press Replace All and then Close. Step 5:

• Go to Cell D5 and refer C5 here. Step 6:

• Pull the Fill Handle to the last. ## Conclusion

Here, we explained how to convert text to date and time in Excel. We showed 5 easy methods here. I hope you can easily find your solution here.

## Related Articles #### Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts 