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.

**Table of Contents**hide

**Download Practice Workbook**

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

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

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.

**Conclusion**

In this article, we described how to split time and date values from an Excel column. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy** and give your suggestions in the comment box.