Excel Formulas to Convert Date String into Date-time ~ Easy Tricks!!
Tags: excelformula, excelformulas
Published: 2021-05-04 06:09:03

---
title: Excel Formulas to Convert Date String into Date-time ~ Easy Tricks!!
published: true
date: 2021-05-03 10:09:36 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/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](https://www.microsoft.com/en-in/microsoft-365/excel)

[![Convert date strings into date-time](https://geekexcel.com/wp-content/uploads/2021/05/Convert-date-strings-into-date-time.png)](https://geekexcel.com/excel-formulas-to-convert-date-string-into-date-time-easy-tricks/convert-date-strings-into-date-time/#main)<figcaption id="caption-attachment-34960">Convert date strings into date-time</figcaption>

## General Formula:

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

**=[LEFT](https://geekexcel.com/how-to-use-left-function-in-microsoft-excel-365/)(date,10)+[MID](https://geekexcel.com/how-to-use-mid-function-in-microsoft-excel-365/)(date,12,8)**

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

**=[DATEVALUE](https://geekexcel.com/how-to-use-datevalue-function-in-excel-365/)([LEFT](https://geekexcel.com/how-to-use-left-function-in-microsoft-excel-365/)(B5,10))**

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

**=[TIMEVALUE](https://geekexcel.com/how-to-use-timevalue-function-in-microsoft-excel-365/)([MID](https://geekexcel.com/how-to-use-mid-function-in-microsoft-excel-365/)(B5,12,8))**

## Syntax Explanations:

- **LEFT** – In Excel, the [**LEFT function**](https://geekexcel.com/how-to-use-left-function-in-microsoft-excel-365/) 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**](https://geekexcel.com/how-to-use-mid-function-in-microsoft-excel-365/).
- **DATEVALUE ** – The **[DATEVALUE function](https://geekexcel.com/how-to-use-datevalue-function-in-excel-365/)** 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](https://geekexcel.com/how-to-use-timevalue-function-in-microsoft-excel-365/)**.
- **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**.

![Input Ranges](https://geekexcel.com/wp-content/uploads/2021/05/Input-Ranges-3.png)<figcaption id="caption-attachment-34957">Input Ranges</figcaption>

- So, apply the **1st** formula to the [**formula bar**](https://geekexcel.com/how-to-use-formula-bar-for-various-purpose-in-excel-365/) section and press the  **ENTER**  key.

![Enter the formula](https://geekexcel.com/wp-content/uploads/2021/05/Enter-the-formula-3.png)<figcaption id="caption-attachment-34955">Enter the formula</figcaption>

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

![Result](https://geekexcel.com/wp-content/uploads/2021/05/Result-4.png)<figcaption id="caption-attachment-34958">Result</figcaption>

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

![Extract Date only](https://geekexcel.com/wp-content/uploads/2021/05/Extract-Date-only.png)<figcaption id="caption-attachment-34956">Extract Date only</figcaption>

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

![Separate Time](https://geekexcel.com/wp-content/uploads/2021/05/Seperate-Time.png)<figcaption id="caption-attachment-34959">Separate Time</figcaption>

## 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](https://geekexcel.com/) **and [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!! **

### Related Articles!!

- **[Excel Formulas to Calculate the Number of Hours between Two Times!!](https://geekexcel.com/excel-formulas-to-calculate-the-number-of-hours-between-two-times/)**
- **[How to Separate Negative and Positive Values From a Column in Excel?](https://geekexcel.com/how-to-separate-negative-and-positive-values-excel/)**
- **[Excel Formulas to Assign Points Based on Late Time ~ Easy Tutorial!!](https://geekexcel.com/excel-formulas-to-assign-points-based-on-late-time/)**