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/PMFinally, 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.
Read More: How to Convert Number to Date in Excel
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.
Read More: How to Convert General Format to Date in Excel
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.
Read More: How to Convert 8 Digit Number to Date in Excel
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.
Read More: How to Convert Number (YYYYMMDD) to Date Format in Excel
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.
Read More: How to Convert Text to Date and Time in Excel
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.
Read More: How to Convert Serial Number to Date in Excel
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.
Download Practice Workbook
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
Get FREE Advanced Excel Exercises with Solutions!
