Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Get nth match with INDEX / MATCH in Excel
  • Count rows that contain specific values in Excel
  • Vlookup Examples in Excel
  • How to get relative column numbers in a range in Excel
  • How to use Excel MMULT Function

Data Analysis Examples

  • How to count table rows in Excel
  • How to Use Solver Tool in Excel
  • How to create a Histogram in Excel
  • Understanding Anova in Excel
  • Managing Conditional Formatting Rules in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation require unique number
  • Excel Data validation no punctuation
  • Excel Data validation don’t exceed total
  • Excel Data validation must contain specific text

Convert date string to date time in Excel

by

This tutorial shows how to Convert date string to date time in Excel using example below.

To convert a date string to a datetime (date with time) you can parse the text into separate components then build a proper datetime.

When date information from other systems is pasted or imported to Excel, it may not be recognized as a proper date or time. Instead, Excel may interpret this information as a text or string value only.

Worked Example:   Count numbers third digit equals 3 in Excel

Formula

=LEFT(date,10)+MID(date,12,8)

Explanation

 

In the example shown, we are using the formulas below.

To extract the date, the formula in C5 is:

=DATEVALUE(LEFT(B5,10))

To extract the date, the formula in d5 is:

=TIMEVALUE(MID(B5,12,8))

To assemble a datetime, the formula in E5 is:

=C5+D5

How these formulas work

To get the date, we extract the first 10 characters of the value with LEFT:

LEFT(B5,10) // returns "2015-03-01"

The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE, which converts the text into a proper Excel date value.

Worked Example:   How to retrieve workbook name only in Excel

To get the time, we extract 8 characters from the middle of the value with MID:

MID(B5,12,8) // returns "12:28:45"

Again, the result is text. To get Excel to interpret as a time, we wrap MID in TIMEVALUE, which converts the text into a proper Excel time value.

To build the final datetime, we just add the date value to the time value.

Worked Example:   How to extract text between parentheses in Excel

All in one formula

Although this example extracts the date and time separately for clarity, you can combine formulas if you like. The following formula extracts the date and time, and adds them together in one step:

=LEFT(date,10) + MID(date,12,8)

Note that DATEVALUE and TIMEVALUE aren’t necessary in this case because the math operation (+) causes Excel to automatically coerce the text values to numbers.

Post navigation

Previous Post:

Convert date to Julian format in Excel

Next Post:

Convert text to numbers using VALUE function in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains one of many things in Excel
  • Count Number of Words in Excel
  • REPLACE, REPLACEB functions: Description, Usage, Syntax, Examples and Explanation
  • How to extract text between parentheses in Excel
  • How to use double quotes inside a formula in Excel
  • How to calculate Quarter of Date in Excel
  • Custom weekday abbreviation in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Get first Monday before any date in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate loan interest in given year in Excel
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYS function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles