Split Date and Time Column in Excel (7 Easy Methods)

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: