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