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.

Data set to convert text to date and time in Excel


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)

DATEVALUE Function in Excel

Step 2:

  • Now, press Enter.

DATEVALUE Function in Excel

Step 3:

  • Pull the Fill Handle till last.

DATEVALUE Function in Excel

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

DATEVALUE Function in Excel

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.

Excel VALUE Function to Convert Text to Date & Time

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)

Excel VALUE Function to Convert Text to Date & Time

Step 3:

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

Excel VALUE Function to Convert Text to Date & Time

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

Excel VALUE Function to Convert Text to Date & Time

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 – Specifies which occurrence of old_text we want to replace with new_text. If we specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in the text is changed to 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.

SUBSTITUTE and VALUE Functions to Convert Text to Date in Excel

Step 2:

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

SUBSTITUTE and VALUE Functions to Convert Text to Date in Excel

Step 3:

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

SUBSTITUTE and VALUE Functions to Convert Text to Date in Excel

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.


Further 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

Use of Mathematical Operators to Convert Text to Date & Time

Step 2:

  • Now press Enter.

Use of Mathematical Operators to Convert Text to Date & Time

Step 3:

  • Now, in Cell D5 write:
=C5
  • Then press Enter.

Use of Mathematical Operators to Convert Text to Date & Time

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:

Convert Text to Date Using Find & Replace Option in Excel

Step 1:

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

Convert Text to Date Using Find & Replace Option in Excel

Step 2:

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

Convert Text to Date Using Find & Replace Option in Excel

Step 3:

  • Replace Dot(.) by Forward Slash (/).

Convert Text to Date Using Find & Replace Option in Excel

Step 4:

  • Press Replace All and then Close.

Step 5:

  • Go to Cell D5 and refer C5 here.

Convert Text to Date Using Find & Replace Option in Excel

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

Leave a reply

ExcelDemy
Logo