Excel Formulas to Convert Date String into Date-time ~ Easy Tricks!!

Assume that you some important data that is mixed with date time and any other text. In this case, how could you separate the date-time? Here we will show the simple and easy ways to convert the date string into the date-time in Excel. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

1. You can use the below formula to convert a date string to a date-time (date with time).

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

1. If you want to separate the date only , you can use the below formula.

=DATEVALUE(LEFT(B5,10))

1. To extract the time only you can use the below formula.

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

Syntax Explanations:

• LEFT – In Excel, the LEFT function helps to extract digits from the numbers as well.
• *MID * – This function will extract the number (starting from the left side) or characters from the given string. Read more on the MID function.
• DATEVALUE ** – The **DATEVALUE function accepts the dates only in the string format and the date should be in the correct format as per the Excel rules.
• TIMEVALUE ** – It converts the given time into an actual Excel time. Read more on the **TIMEVALUE function.
• Comma symbol (,) – It is a separator that helps to separate a list of values.
• Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Refer to the below example image.

• First, we will enter the input date string in Column B.
• Here we are going to separate the date-time.

• So, apply the 1st formula to the formula bar section and press the ENTER key.

• Finally, we will get the result in the selected cell.

• Now we need to separate the date and time separately.
• Enter the 2nd formula with the DATEVALUES function, and you can get the dates only.

• After that apply the 3rd formula with the TIMEVALUES function, it will separate the time from your text string and display the result as shown below.

Closure:

From this article, you can get some clarification on how to convert the date string into date-time, date, and time in Excel with different formulas. Hope you like this article!! Please feel free to state your query or feedback for the above article. Click here to know more about *Geek Excel **and Excel Formulas *!! **

Related Articles!!

• Excel Formulas to Calculate the Number of Hours between Two Times!!
• How to Separate Negative and Positive Values From a Column in Excel?
• Excel Formulas to Assign Points Based on Late Time ~ Easy Tutorial!!