# How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Very often we find time and date in text format in a worksheet. This may be a result of the wrong input format. This may also occur when we copy data from other sources. However, to perform any operation with these time and date data, we need to convert it to date format. Doing this is a very simple operation. In this article, we will show you how to convert text date and time to date format in Excel using 7 easy ways.

We have a dataset where several dates and times are shown. The date and time values are in the Text Format column. We can recognize whether a value is in text format or not by using a simple technique. Any text value will be aligned to the left side of a cell. In this case, all the values are in text format as all of the values are aligned to the left side of the cells. We will fix this using 7 easy ways.

## How to Convert Text Date and Time to Date Format in Excel: 7 Easy

Now we will discuss 7 easy ways to convert text date and time to date format in Excel in step by step.

### 1. Convert Text Date and Time to Date Format Using DATEVALUE and TIMEVALUE Function

The DATEVALUE functionÂ converts any date into a numerical value. The TIMEVALUE function converts any time into a numerical value. We can use a combination of these two functions to convert date and time to date format.
STEPS:
To do so, in cell C5, we type,

`=DATEVALUE(B5)+TIMEVALUE(B5)`

Formula Breakdown

`DATEVALUE(B5)`>> Converts the date into a numerical value.
Â  Â  Â  Â  Output is>> 44208
Â  Â  Â  Â  Explanation>> Date value of 44208 is 12-01-2021

Â `TIMEVALUE(B5)`>> Converts the time into a numerical value.
Â  Â  Â  Â Output is>> 0.52986111111386
Â  Â  Â  Â Explanation>>Â Time value of 0.52986111111386 is 12:43:00 PM

`DATEVALUE(B5)+TIMEVALUE(B5)`>> Adds up the numerical values of date and time.
Â  Â  Â  Â Output is>> 44208.5298611111
Â  Â  Â  Â Explanation>>Â  44208 denotesÂ DateÂ  and 0.52986111111386 denotesÂ Time.

• Pressing the ENTER key would get the resultant value in the C5 cell.

Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel, we will get values in corresponding cells.

• Now we need to format this value in our desired format. To do so, we will go to the Number Toolbar and click on More Number Formats.

• A new dialogue box will pop up. We will select Custom and type our desired data format. In this case, we will type
`dd/mm/yyyy hh:mm AM/PM`

Finally, click OK.

Format Breakdown

`dd/mm/yyyy hh:mm AM/PM`>> This a custom format we used to convert the text date and time to date format.

`dd` â€”-> It denotes the date.
`mm` â€”-> It denotes month.
`yyyy` â€”-> This denotes the year.
`hh` â€”-> It denotes hour.
`mm` â€”-> It denotes minute.

Then `AM` or `PM` will appear depending on the time.

We will get the formatting as the picture below.

The values have been converted to Date Format as they start from the right side of the cells.

Read More: How to Convert Text to Date in Excel

### 2. Apply VALUE Function to Convert Text Date and Time to Date Format

The VALUE function in Excel converts any value representing the number in any format into a numerical value. We can use this function to convert date and time in text format into date format. We will see it in different steps.

STEPS:

• To do so, in cell C5, we type,
`=VALUE(B5)`

Formula Breakdown

`VALUE(B5)`>> Converts the value in B5 cell into numerical value.
Â  Â  Â  Output is>> 44208.5298611111
Â  Â  Â  Explanation>> Numerical value of 12-01-2021Â  12:43:00 PM

• Pressing the ENTER key, would get the resultant value in the C5 cell.

• Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel we will get values in corresponding cells.

• Following the steps described in method 1 we will be able to convert these values into time format.

### 3. Using Text to Column Wizard to Convert Text Date and Time to Date Format

In the Data tab, there is a feature named Text to Columns. We can use this feature to convert text date and time to date format.

STEPS:

• To do so, first, we will select the data range. In this case, we will select the Text Format column.

We will go to the Data tab and select Text to Columns from Data Tools.

A new dialogue box will pop up. We need to select Delimited and click Next.

Now, we will unmark all the check boxes and click Next.

Then, we are going to select Date and select the desired format. Next, we will select the Destination cell address. After that, we will click Finish. We can see the preview in the black region of the dialogue box.

Our formatted data will be on the destination cell address we selected.

Following the steps described in method 1 we will be able to convert these values into time format.

### 4. Apply Paste Special to Convert Text Date and Time to Date Format

We can use the Paste Special option of Excel to convert text date and time to date format using the following steps.

STEPS:

• First, we will select some empty cells and select the format we want to put our data in those cells. We can choose the format of our choice using the steps described in method 1.

Then we will copy those cells by pressing CTRL + C.

• Then we will select our data range (in this case Text Format column) and select Paste Special from the Home.

A new dialogue box will pop up. We will click Add and select Ok.

We will get our data formatted.

### 5. Fixing dates with two-digit years to Convert Text Date and Time to Date Format

Sometimes our data has only two digits as years. We can fix this easily in Excel. Notice the little green triangles on the right side of the cells. They denote that there is some sort of anomaly with our data.

If we select the range, we will see an exclamatory(!) sign.

STEPS:
Clicking on that exclamatory(!) sign we will see the option to convert our two digit years into four digit years.

• The years of our data will be converted into four digits.

• We can convert these values into date format using method 1.

### 6. Using Find and Replace to Convert Text Date and Time to Date Format

We can use the Find and Replace dialogue box to convert text date and time to date format.

STEPS:

• We first copy the data in the Formatted Date column from the Text Format column.

• Then we will go to the Find and Replace dialogue box by pressing CTRL + H. In Find what box we will type â€“ and in Replace with box we will type / . After that we will select Replace All.

A Message Box will appear confirming the replacement procedure.

• Using the steps described in method 1 we can format the values to date format.

### 7. Converting Text Date and Time to Date Format Using Mathematical Operations

The simplest of mathematical operations will allow us to convert text date and time to date format. We simply need to perform a mathematical operation and bingo! our data will be in date format.

STEPS:

• To do this, we need to simply divide the value by 1. To do this, in cell C5 we type
`=B5/1`

• Pressing the ENTER key, we will get the value in Date format.

• Now by dragging down or double-clicking the Fill Handle to use the AutoFill feature of Excel, we will get values in corresponding cells.

## Things to Remember

We cannot perform any operation with dates if those arenâ€™t in date format. So, be sure to convert to date format before performing any operation with dates.

## Practice Section

We have included a practice section in the worksheet provided so that you can exercise the methods yourself.

## Conclusion

In this article, we have demonstrated how to convert text date and time to date format in Excel. We have discussed 7 easy ways in detail. Practice these methods on your own and let us know if you face any issues. Please leave your comments and feedback below.

## Related Articles

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutful Hamid

LUTFUL HAMID is an outstanding marine engineer who finds joy in navigating the realms of Excel and diving into VBA programming. To him, programming is like saving time when dealing with data, files, and the internet. His skills extend beyond the basics, covering Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he's shifted gears and now serves as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF