If you have a date and time in the same cell and need to separate them into two different columns, you are in right place! In this article, I will show you 7 easy techniques to split date and time column in Excel with suitable examples and proper illustrations.

The following pictures highlight the purpose of this article.

**How to Split Date and Time Column in Excel: ****7 Methods**

Let’s get introduced to the dataset first. It contains date and time combinedly in each cell of the column.

Now, apply the following techniques to separate them into columns.

**1. Use the INT Function to Split Date and Time**

**Excel** considers dates as integer numbers (starting from **Jan 1, 1900**) and times as fractions. So, we can separate the date part from the data using the **INT** function, then subtracting this result from the original data will return the time in Excel’s numeric format. Later we will apply the custom time format to the fraction to get it in a known format.

The** INT** function rounds a number to the nearest integer.

The **INT **function considers the date as a decimal number. It detects the integer part as the date value.

**Steps:**

- First, add two new columns for data and time.

- Go to cell
**C4**and put the following formula.

`=INT(B4)`

- Now, press the
**Enter**key and drag the**Fill Handle**icon.

We can see that dates are showing with **00:00:00** time. To remove this time value need to format the cells.

- Now, we will change the format of the cells to present the date value exactly.
- Select the cells of the Date column and press the right button on the mouse.
- Choose the
**Format Cells**option from the**Context Menu**.

- Now, from the
**Custom**section, we choose our format or we can put our desired format on the marked box. - Then, press
**OK**.

- Now, look at the dataset.

Dates are formatted according to our needs.

This **INT **function separates the date part first. Now, we need to find out the time value.

- Now, go to
**Cell D4**of the**Time**column and put the formula below.

`=B4-C4`

We just subtract the date value from the main data.

- Again, hit the
**Enter**key and drag the**Fill Handle**icon.

We can see that time is not showing exactly.

- We need to change the format of those cells. Go to the
**Format Cells**option as mentioned before. - Select the
**Time**column and fix the format from the**Time**Finally, press**OK**.

- Look at the dataset now.

Finally, we split the date and time column.

**2. Split Date and Time Using Excel DATE Function **

In this section, we will use a formula based on the** DATE, YEAR, MONTH, **and **DAY** functions together to get the date value. After that, this value will be used to get the time value.

The** DATE** function returns the number that represents the date in the Microsoft Excel date-time code.

The** YEAR** function returns the year of a date, an integer in the range **1900-9999**.

The** MONTH** function returns the month, a number from **1 **(**January**) to **12 **(**December**).

The **DAY** function returns the day of a month, a number from **1** to **31**.

**Steps:**

- Go to
**Cell C4**of the**Date**column. - Put the following formula on that cell.

`=DATE(YEAR(B4),MONTH(B4),DAY(B4))`

- Now, press
**Enter**and drag the**Fill Handle**icon downwards.

We get the date value. This date value will be subtracted from the **Data **column to get the time value.

- Put the formula below on cell
**D4.**

`=B4-C4`

- Now, double click the
**Fill Handle**icon. - After applying the
**Time**format, you will get results like the following.

**3. Use the TIME Function to Split Date and Time**

In this section, we will make a formula combining those 4 functions to get the time value first. After that, we will determine the date value. Then we will a custom date format to them.

The **HOUR, MINUTE, **and **SECOND** functions extract the hours, minutes, and seconds values from the given reference and are used as the argument of the **TIME **function.

The **TIME **function converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format.

The **HOUR** function returns the hour as a number from **0** (**12 A.M.**) to **23** (**11 P.M.**)

The **MINUTE** function returns the minute, a number from **0** to **59**.

The** SECOND** function returns the second, a number from **0** to **59**.

**Steps:**

- Go to cell
**D4**of the**Time**column and put the formula below.

`=TIME(HOUR(B4),MINUTE(B4),SECOND(B4))`

- Hit the
**Enter**button and drag the**Fill Handle**icon.

We get the time value.

- Now, subtract the time value from the
**Data**column and get the**Date**Put the following formula at cell**C4**for that.

`=B4-D4`

- Now, drag the
**Fill Handle**icon to get the values for all the cells. Again, don’t forget to change the number format to a custom date. The result will be as the below. 👇

**4. Using the TEXT Function**

We can extract both the times and the dates using the **TEXT **function.

The** TEXT** function converts a value to text in a specific number format.

**Steps:**

- First, go to cell
**C4**of the**Date**column and put the following formula.

`=TEXT(B4,"mm/dd/yyyy")`

- Press
**Enter**and drag the**Fill Handle**icon.

We get the date value.

- Now, put the following formula on cell
**D4**to determine the time value.

`=TEXT(B4,"hh:mm:ss")`

- Double click the
**Fill Handle**icon.

Finally, we split the time and date values from the column.

**5. Use of Text to Columns Feature**

Apart from using formulas, we can apply the **Excel Text to Columns** feature to split the date and time from the column more easily. Follow the steps below for that.

**Steps:**

- Choose all the cells of the
**Data**column. - Go to the
**Data**tab now. - Choose the
**Text to Columns**option from the**Data Tools**group.

- Mark the
**Fixed width**option from the**1st Step**of**Text to Columns Wizard**. - Then, press
**Next**.

- In the
**2nd step**, go to the**Data preview**section and set the position. Again, press**Next**.

- In the final step, choose the
**General**option as default. Then click on the**Finish**button.

- Look at the data set.

Time value is separated. In the **Data **column, all the time values are zero. Only date values are presented here.

**6. Use the Flash Fill Feature**

If you write something in Excel with a regular pattern, the **Flash Fill** feature can sense that and it is also able to reproduce the pattern. So we can easily use this awesome feature to split dates and times from a single column into two.

**Steps:**

- Input values in the first
**2**cells of the**Date**and**Time**column from the**Data**column to create a pattern.

- Now, select all the cells of the
**Date**column and go to the**Data**tab. - Choose the
**Flash Fill**option of the**Data Tools**.

- Look at the dataset now.

- Similarly, apply this technique after selecting all the cells of the
**Time**column.

**Flash Fill**by pressing

**Ctrl+E**.

**7. Use Power Query to Extract Date and Time Separately**

**Excel Power Query** is an amazing tool. We can use this tool to split the date and time values. Follow the steps below.

**Steps:**

- First, select all the cells of the
**Data**column. - Then choose
**From Table/Range**from the**Data**tab.

- A pop-up will appear with the selected range.
- Mark the box of
**My table has headers**and press

- The
**Power Query**window appears now.

- Now, go to the
**Add Column**tab. - Then, choose
**Date Only**from the**Date**feature.

- Look at the dataset.

All the dates are separated and form a new column.

- Again, choose the
**Add Column**tab. - Now, select
**Time Only**from the**Time**feature.

- Have a look at the dataset.

Finally, we get both the dates and time value.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we described how to split time and date values from an Excel column. I hope this will satisfy your needs.

**Related Articles**

- How to Split Column in Excel by Comma
- How to Split Column by First Space in Excel
- Split Column in Excel by Word
- How to Split One Column into Multiple Columns in Excel Formula