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


Download Practice Workbook


7 Easy Ways to Convert Text Date and Time to Date Format in Excel

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.

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

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 (10 ways)


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)

how to convert text date and time to date format in excel

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.

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

  • 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 (6 Easy Ways)


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.

how to convert text date and time to date format in excel

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.

how to convert text date and time to date format in excel

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.

how to convert text date and time to date format in excel

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.

how to convert text date and time to date format in excel

Read More: How to Convert General Format to Date in Excel (7 Methods)


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.

how to convert text date and time to date format in excel

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.

how to convert text date and time to date format in excel

We will get our data formatted.

Related Content: Text Won’t Convert to Date in Excel (4 Problems & Solutions)


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 in the right side of the cells. They denote that there is some sort of anomaly with our data.

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

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

Read More: Excel VBA to Convert Date and Time to Date Only


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

We can use 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.

how to convert text date and time to date format in excel
A Message Box will appear confirming the replacement procedure.

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

how to convert text date and time to date format in excel

Read More: How to Convert Week Number to Date in Excel (2 Suitable Methods)


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.

how to convert text date and time to date format in excel

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

how to convert text date and time to date format in excel

Read More: Convert Serial Number to Date in Excel (7 Easy Ways)


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 comment and feedback below. For any of your Excel-related queries please have a look at our website. Our team would be happy to help you out.


Related Articles

Lutful Hamid

Lutful Hamid

Hello! Welcome to my Profile. I have completed BSc in Naval Architecture and Marine Engineering from BUET and MBA from IBA. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo