How to Separate Date and Time in Excel?

There may be times when you import datasets and find that date and time columns have been combined.

In this tutorial, we are going to look at seven simple methods to separate date and time in Excel. 

So, let’s get started.

How to Separate Date and Time Using the Text to Columns Wizard

Text to Columns is an easy way to quickly separate dates and times in separate columns in Excel.

Below I have a sample dataset containing a list of dates with times in a single column.

To split the date and time into separate columns, using the Text to Columns Wizard, do the following.

1. Select range A2:A5.

2. Go to the Data Tab. In the Data Tools Group, select Text to Columns.

3. You should see the Convert Text to Columns Wizard. With the Delimited option selected, click the Next button.

4. For Step 2, check the Space option and uncheck any other option if checked

5. Change the Destination cell to B2 as shown below and click Finish. This ensures that we get our result in a separate column (and the original text is not overwritten)

The above steps would give you the result as shown below:

To see the times in Column C, without the seconds incorporated in the formatting, do the following.

1. Select range C2:C5.

2. Now go to the Home Tab. In the Number Group, select the drop-down and choose the More Number Formats… option.

3. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample.

4. Click Ok. You should see the following.

The Text to Columns Wizard is a handy feature that you can utilize, to easily split columns based on a delimiter. 

In our example, we used the Text to Columns Wizard to separate the date and time into two separate columns.

Since there is a space between each date and time, we have used space as the delimiter to separate the date and time values in separate columns.

In addition, we are specifying the General format using the Wizard, so all dates are converted to date format.

This is a simple method that is easy to understand and use.

Note: If you aren’t getting the results that you desire, check your Regional Date and Time Settings. You will need to see what your current date and time formats are and change them if needed. 

Also read: How to Insert Date in Excel?

How to Separate Date and Time Using the INT Function

Since dates are stored as integer values in Excel and time is stored as decimal values, if you want to separate the date and time values in separate columns, you can also do that by separating the integer and the decimal values and then formatting them as date and time.

Below I have a sample dataset containing a list of dates with times in a single column (column A).

To split the date and time into separate columns using the INT Function, use the below steps.

1. Select range B2:B5.

2. Go to the Home Tab. In the Number Group, select the drop-down and apply the Short Date format.

3. In cell B2, enter the following formula.

=INT(A2)

4. Drag the formula down the column to see the following.

5. Now select range C2:C5.

6. Go to the Home Tab. In the Number Group, select the drop-down and choose the More Number Formats… option.

7. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample. Click Ok.

8. In cell C2, enter the following formula.

=A2-INT(A2)

9. Drag the formula down the column to see the following.

How does this work?

Dates are stored as serial numbers in Excel. A date with a time has the time segment represented by the decimal portion of the number.

Bear in mind that in order to see an actual date/date with time, we need to apply the needed formatting.

In column B, we are using the INT Function to round the serial number representing the date down to the nearest integer. In other words, we will then have the date on its own, without the time portion.

The syntax of the INT Function is:

=INT(number)

• number – this is a number that contains a decimal that you would like to be rounded down to the nearest integer. It is a required value.

In column C, we are performing simple subtraction. Remember our dates are stored as numbers, to get the time portion of the date (in this case the decimal value) on its own.

Also read: How to Remove Year from Date in Excel?

How to Separate Date and Time Using the VALUE, TEXTBEFORE, VALUETOTEXT, and TEXTAFTER Functions

Below I have a sample dataset containing a list of dates with times in a single column.

To split the date and time into separate columns, using the VALUE, TEXTBEFORE, VALUETOTEXT, and TEXTAFTER functions do the following.

1. Select range B2:B5.

2. Go to the Home Tab. In the Number Group, select the drop-down and apply the Short Date format.

3. In cell B2, enter the following formula.

=VALUE(TEXTBEFORE(VALUETOTEXT(A2),"."))

4. Drag the formula down the column to see the following.

5. Select range C2:C5.

6. Go to the Home Tab. In the Number Group, select the drop-down and choose the More Number Formats… option.

7. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample. Click Ok.

8. In cell C2, enter the following formula.

=VALUE("0."&TEXTAFTER(VALUETOTEXT(A2),"."))

9. Drag the formula down the column to see the following.

Let me quickly also explain how this works.

The TEXTBEFORE Function is a new function introduced in Office 365 that makes extracting text much easier than in earlier versions of Excel.

The TEXTBEFORE Function extracts all the characters of a text string that occur before a certain specified delimiter. This delimiter can be a space, a colon or a point, etc.

The VALUETOTEXT Function does as the name implies, which is that it converts any value to text.

So let me explain a little bit about how the formula in Column B works.

In part of our formula in cell B2, we are using the VALUETOTEXT Function so that the underlying number value in cell A2 will be treated as text.

Remember dates are stored as numbers. The TEXTBEFORE Function then takes what has been input as text, and extracts all the characters before the point. So that means we are isolating the integer portion of the text on its own.

The VALUE Function is then used to convert this text to a number. Since date formatting has been applied, we thus see the date on its own in Column B.

Now likewise, the formula in Column C works following a similar premise. The TEXTAFTER Function, however, extracts all the characters of a text string that occur after a certain specified delimiter. This delimiter can be a space, a colon or a point etc.

In part of our formula in cell C2, we are using the VALUETOTEXT Function, so that the underlying number value in cell A2 will be treated as text. 

Remember dates are stored as numbers. The TEXTAFTER Function then takes what has been input as text, and extracts all the characters after the point. So that means we are isolating the decimal portion of the text on its own.

However, we have to tell Excel that this is a decimal, so to do this we add 0. as text using the concatenation operator.

The VALUE Function is then used to convert this entire text string to a number. Since our specified time formatting has been applied, we thus see the time on its own in Column C.

Also read: How to Convert Date to Serial Number in Excel?

How to Separate Date and Time Using the Flash Fill Feature

Below I have a sample dataset containing a list of dates with times in a single column.

To split the date and time into separate columns, using the Flash Fill Feature, do the following.

1. In cell B2, type the date from cell A2 without the time.

2. In cell B3, type the date from cell A3 without the time.

3. Select cell B4.

4. Go to the Data Tab. In the Data Tools Group, select Flash Fill.

5. You should see the following.

6. Now in cell C2, enter the time from cell A2 without the date.

7. In cell C3, enter the time from cell A3 without the date.

8. Select cell C4.
9. Go to the Data Tab. In the Data Tools Group, select Flash Fill. You should see the following.

If we have the Flash Fill feature turned on then Excel can detect patterns. This is based on what we show Excel.

So in this case in Column B, we are showing Excel that we want only the date isolated from the adjacent cell. That’s why we entered the date on its own for the first two cells.

In Column C, we are showing Excel that we want only the time portion of the value, in Column A.

Note: Flash Fill worked in our example, but this is not always the case. So when using Flash Fill always double-check your results to see that you are getting the desired outcome.

How to Separate Date and Time Using the TEXT Function

Below I have a sample dataset containing a list of dates with times in a single column.

To split the date and time into separate columns using the TEXT Function do the following.

1. Select cell B2 and enter the following formula.

=TEXT(A2,"mm/dd/yyyy")

2. Drag the formula down the column to see the following.

3. In cell C2, enter the following formula.

=TEXT(A2,"hh:mm")

4. Drag the formula down the column to see the following.

The TEXT Function is used to format and display numbers in your desired way based on a format code.

In Column B, we are inputting a format code, that is telling Excel to display the dates in column A without the time.

Likewise in Column C, we are inputting a format code that is telling Excel to display the dates in column A with only the time portion.

You will see that the values are left-aligned instead of right-aligned, which means that they will subsequently be treated as text.

Thus this method is ideal if you just need to print this sheet for example, and need the dates and times separated. 

However, if you need to perform other date and time-related calculations with the values in Column B and C then consider using another method.

How to Separate Date and Time using the TRUNC Function

Below I have a sample dataset containing a list of dates with times in a single column.

To split the date and time into separate columns using the TRUNC Function do the following.

1.  Select cell range B2:B5.

2. Go to the Home Tab. In the Number Group, select the drop-down and apply the Short Date format.

3. In cell B2, enter the following formula.

=TRUNC(A2)

4. Drag the formula down the column to see the following.

5. Select range C2:C5.

6. Go to the Home Tab. In the Numbers Group, select the drop-down and choose the More Number Formats option.

7. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample. Click Ok.

8. In cell C2, enter the following formula.

=A2-B2

9. Drag the formula down the column to see the following.

Like we did for our INT Function example, we are again taking advantage of the fact that Excel stores dates as serial numbers. The TRUNC Function is used to remove the fraction or decimal part of a number. 

So in Column B, we are isolating only the integer part of our number and thus we will see only the date without the time.

In column C, we are performing simple subtraction to get the time portion of the date (in this case the decimal value) on its own.

How to Separate Date and Time Using VBA

Below I have a sample dataset containing a list of dates with times in a single column.

To split the date and time into separate columns using VBA code do the following.

1. Select cell range A2:A5.

2. Go to the Developer Tab. In the Code Group, click Visual Basic.

3. Go to Insert and select Module.

4. Copy and paste the following code.

'Code Developed by Steve Scott from spreadsheetplanet.com
Sub Splitting_Dates_And_Times()
Dim distinct_date As Variant
Dim distinct_time As Variant

For Each cell In Selection
distinct_date = Int(cell)
formatted_date = Format(distinct_date, "mm / dd / yyyy")
cell.Offset(0, 1).Value = formatted_date
distinct_time = cell - Int(cell)
formatted_time = Format(distinct_time, "hh:mm")
cell.Offset(0, 2).Value = formatted_time
Next cell

End Sub

5. Press F5 on your keyboard to run the code and you should see the following.

VBA contains equivalent functions to the INT worksheet and FORMAT functions.

In the above code, we are telling Excel to loop through all the cells in the selection.

For each cell, we use the INT Function to get only the integer part of the value in the cell. We use the format function to specify our desired formatting.

We then use the OFFSET function to say that this integer (i.e the date on its own) should be placed in the cell adjacent to the one selected in Column A.

We use the INT Function with simple subtraction to get the time on its own.

While still within the Loop structure, we specify that this value should be placed in the cell two cells, from the one selected in Column A.

How to Separate Date and Time Using Power Query

Power Query can be useful in separating date and time then you need to do this on a regular basis.

Below I have a sample dataset containing a list of dates with times in a single column.

To split the date and time into separate columns using Power Query do the following. We will create a Table first.

1. Select any cell in the data set. Go to the Insert Tab and in the Tables Group, select Table.

2. Click Ok to create a Table.

3.  You should see the following.

Now we need to open the Power Query Editor.

1.  Select one cell in the Table. Go to the Data Tab. In the Get & Transform Data Group, click on the From Table/Range option.

2.  You should see the Table loaded in the Power Query Editor.

3. Go to the Home Tab. In the Transform Group, click on the Split Column option.

4. Choose the By Delimiter option.

5. You should see the Split Column By Delimiter Window.

6. Choose Space as the Delimiter. For the Split at option, select the Left-most delimiter.

7. Click Ok. You should see the following.

8. Right-click the Header Date and Time.1 and select Rename…

9. Change the Name to Date.
10. Do likewise for the Date and Time.2 Column and change the name to Time.

11. Go to the Home Tab. In the Close Group, click on the Close & Load drop-down. Choose the Close & Load option. You should see the following.

The benefit of Power Query is that you do not need to repeat these steps every time you get a new data set. you can create the query once and whenever you get a new data set, you can modify the query and connect it to the new data and run the steps by doing a simple refresh.

In the above example, if our original data set changes, all we need to do is right-click on any cell of the resulting table and click Refresh.

You can also connect your power query to another table in the same workbook or another workbook and run the same steps to quickly separate the date and time values.

In this tutorial, I have provided a comprehensive overview of how to separate dates and times in Microsoft Excel. I hope you will find this tutorial useful when working with date and time calculations in Excel.

Other Excel articles you may also like:

• How To Combine Date and Time in Excel (3 Easy Ways)
• How to Highlight Dates Before Today in Excel?
• Calculate Days Between Two Dates in Excel (Workdays/Weekends)
• How to Change Date and Time to Date in Excel (4 Easy Ways)
• How to Calculate Hours between two Times in Excel?
• How to Calculate the Number of Months Between Two Dates in Excel?
• How to Round Time to the Nearest Quarter Hour in Excel?
• How to Add Hours to Time in Excel?