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.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

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

**Similar Readings:**

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