# How to Convert Text Date and Time to Date Format in Excel – 7 Methods

In the dataset below date and time values are in Text Format (they are left aligned).

### Method 1 – Convert Text Date and Time to Date Format Using the DATEVALUE and the TIMEVALUE Function

STEPS:

• In C5, enter:
=DATEVALUE(B5)+TIMEVALUE(B5)

Formula Breakdown

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

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

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

• Press ENTER to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Go to the Number Toolbar and click More Number Formats.

• Select Custom and enter a date format. Here:
dd/mm/yyyy hh:mm AM/PM
• Click OK.

Format Breakdown

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

dd —->  day.
mm —-> month.
yyyy —-> year.
hh —->  hour.
mm —-> minute.

AM or PM will be displayed depending on the time.

This is the output.

Read More: How to Convert Text to Date in Excel

### Method 2 – Applying the VALUE Function to Convert Text Date and Time to Date Format

STEPS:

• In C5, enter:
=VALUE(B5)

Formula Breakdown

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

• Press ENTER to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Follow the steps described in Method 1 to convert the values into time format.

### Method 3 – Using the Text to Columns Wizard to Convert Text Date and Time to Date Format

STEPS:

• Select the data range. Here, Text Format column.

• Go to the Data tab and select Text to Columns in Data Tools.

• Select Delimited and click Next.

• Uncheck all check boxes and click Next.

• Select Date and a date format.
• Enter the cell address in Destination.
• Click Finish.

This is the output.

• Follow the steps described in Method 1  to convert these values into time format.

### Method 4 – Applying the Paste Special Feature to Convert Text Date and Time to Date Format

STEPS:

• Select empty cells and choose a date format (follow the steps described in Method 1).

• Copy the cells by pressing CTRL + C.
• Select the data range (here, Text Format column) and choose Paste Special in the Home tab.

• Select Add and click OK.

.

### Method 5 – Convert Text Date and Time (dates with two-digit years) to Date Format

• Select Column B.  You will see an exclamation mark (!).

STEPS:

• Click the exclamation mark (!) and choose the option shown below (convert two digit years into four digit years).

This is the output.

• Convert these values into date format following the steps described in Method 1.

### Method 6 – Using the Find and Replace Feature to Convert Text Date and Time to Date Format

STEPS:

• Copy the data in the Text Format column and paste it in the Date column.

• Open the Find and Replace dialogue box by pressing CTRL + H.
• In Find what, enter and in Replace with, enter / .
• Click Replace All.
• In the Message Box, click OK.

• Follow the steps described in Method 1 to format the values into date format.

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

STEPS:

• In C5, enter:
=B5/1

• Press ENTER to see the result.

You will get the value in Date format.

• Drag down the Fill Handle to see the result in the rest of the cells.

Practice here.

## Related Article

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

Get FREE Advanced Excel Exercises with Solutions!
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