How to Convert Text to Date and Time in Excel (5 Easy Ways)

Here’s a brief overview of how you can convert various text formats into the date formatting in Excel.

We will discuss several functions that can convert text into date and time. We’ll use a simple dataset containing various dates in text form.

Method 1 – Using the 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)

Steps:

• Go to cell C5 and insert the following formula.

• Press Enter and pull down the Fill Handle to the end of the column.

• In the value column, we will get the numeric values, but we want the date values here.
• To format cells into the date, press Ctrl + 1 key.
• The Format Cells dialog window appears. Choose Date from Category and select a date format.

• For example, in the sample datasheet, the Date Column is formatted as mentioned already. In cell D5, use the formula:

• Press Enter and drag the Fill Handle down.

Note:

In row 8 of the value and date column, we do not get any value as the DATEVALUE function cannot convert a numeric value.

Read More: How to Convert Text to Date in Excel

Method 2 – Applying the Excel VALUE Function to Change Text to Date and Time

Syntax

=VALUE(text)

Steps:

• We inserted time values in cells B5 and B6.

• Move to cell C5 of the Value column and enter the following formula:

The VALUE function can convert any value to a number. So, we get the date and time in serial number format.

• Press Enter and drag the cell down.

• We get decimal values in the corresponding cells where time exists.
• Move to cell D5 to get the date and time by inputting the formula:

• Press Enter and drag the cell down.

Read More: How to Convert Number to Date in Excel

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

Steps:

• Modify the data in the Text column using periods. The period won’t be converted properly by conversion function.

• In cell C5, use the formula to replace Dot (.) with Forward slash (/):

• Press Enter and use AutoFill on the other cells.

• In cell D5, insert the following formula to get the Date:

Read More: How to Convert General Format to Date in Excel

Method 4 – Converting Text to Date and Time with Mathematical Operators

Steps:

• Move to the C5 cell and insert a reference to the B5 cell.
• Insert an Addition (+) sign and input a zero (0) after it. The final formula will be:

• Press Enter.

• In cell D5, use the following formula:

• Press Enter.

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

• Drag down the Fill Handle tool for other outputs.

Read More: How to Convert 8 Digit Number to Date in Excel

Method 5 – Using the Excel Find and Replace Option to Convert Text to Date and Time

Let’s modify the data slightly to use various date formatting types inside the text values.

Steps:

• Copy the data from the Text column to the Formatting column.
• Select the range B5:B9 and type Ctrl + H.

• The Find and Replace window pops out. Replace Dot(.) with Forward Slash (/).
• Press Replace All.

• Move to cell D5 and refer C5 inside it. Drag it down to the last cell to get the following output.

Download the Practice Workbook

Related Articles

• How to Convert Serial Number to Date in Excel
• How to Convert Number (YYYYMMDD) to Date Format in Excel
• How to Convert Text Date and Time to Date Format in Excel
• How to Disable Auto Convert to Date in Excel
• Text Won’t Convert to Date in Excel

<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel