How to Separate Date and Time in Excel (7 Easy Ways)

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.

Dataset with date and time in one column

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

  1. Select range A2:A5.
Select the range
  1. Go to the Data Tab. In the Data Tools Group, select Text to Columns.
Click Text toColumns
  1. You should see the Convert Text to Columns Wizard. With the Delimited option selected, click the Next button.
click the Next button
  1. For Step 2, check the Space option and uncheck any other option if checked
check space as the delimiter
  1. 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)
change the destination

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

Date and time in separate columns

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

  1. Select range C2:C5.
Select cells with time
  1. Now go to the Home Tab. In the Number Group, select the drop-down and choose the More Number Formats… option.
Click on More Number formats
  1. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample.
Select a format for cells with time
  1. Click Ok. You should see the following.
Time formatted as selected

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. 

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

Dataset with date and time in one column

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

  1. Select range B2:B5.
Select cells where date result is expected
  1. Go to the Home Tab. In the Number Group, select the drop-down and apply the Short Date format.
Apply short date format
  1. In cell B2, enter the following formula.
=INT(A2)
INT formula
  1. Drag the formula down the column to see the following.
Drag the formula for the entire column
  1. Now select range C2:C5.
Select cells that will have time value
  1. Go to the Home Tab. In the Number Group, select the drop-down and choose the More Number Formats… option.
Click on More number format
  1. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample. Click Ok.
Select the time format
  1. In cell C2, enter the following formula.
=A2-INT(A2)
Formula to ger time from date and time
  1. Drag the formula down the column to see the following.
Drag the formula for the entire column

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.

Dataset with date and time in one 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.
Select the cells where date would come
  1. Go to the Home Tab. In the Number Group, select the drop-down and apply the Short Date format.
Click on Short date format
  1. In cell B2, enter the following formula.
=VALUE(TEXTBEFORE(VALUETOTEXT(A2),"."))
Formula to get date
  1. Drag the formula down the column to see the following.
apply the formula for the entire column
  1. Select range C2:C5.
Select cells with time
  1. Go to the Home Tab. In the Number Group, select the drop-down and choose the More Number Formats… option.
Click on More Number formats
  1. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample. Click Ok.
Select the time format you want
  1. In cell C2, enter the following formula.
=VALUE("0."&TEXTAFTER(VALUETOTEXT(A2),"."))
Enter formula to get the time value
  1. Drag the formula down the column to see the following.
Time separated from the date

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.

Dataset with date and time in one 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.
Enter date without time in cell A2
  1. In cell B3, type the date from cell A3 without the time.
enter expected result in cell A3 too
  1. Select cell B4.
Select cell A4
  1. Go to the Data Tab. In the Data Tools Group, select Flash Fill.
Click on Flash Fill
  1. You should see the following.
Column filled with dates without time
  1. Now in cell C2, enter the time from cell A2 without the date.
Enter time in cell C2
  1. In cell C3, enter the time from cell A3 without the date.
Enter time in cell C3
  1. Select cell C4.
  2. Go to the Data Tab. In the Data Tools Group, select Flash Fill. You should see the following.
Use Flash fill

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.

Dataset with date and time in one 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")
Enter TEXT formula
  1. Drag the formula down the column to see the following.
Drag the formula for the entire column
  1. In cell C2, enter the following formula.
=TEXT(A2,"hh:mm")
TEXT formula to get time
  1. Drag the formula down the column to see the following.
Drag the formula for the entire column

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.

Dataset with date and time in one column

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

  1.  Select cell range B2:B5.
Select cells
  1. Go to the Home Tab. In the Number Group, select the drop-down and apply the Short Date format.
Select Short Date format
  1. In cell B2, enter the following formula.
=TRUNC(A2)
Enter TRUNC formula
  1. Drag the formula down the column to see the following.
Drag the formula for the entire column
  1. Select range C2:C5.
Select time cells
  1. Go to the Home Tab. In the Numbers Group, select the drop-down and choose the More Number Formats option.
Select more number formatting
  1. Using the Format Cells Dialog Box, ensure the Time format is selected. Choose the following sample. Click Ok.
Select time format
  1. In cell C2, enter the following formula.
=A2-B2
Enter the formula to get time only
  1. Drag the formula down the column to see the following.
Drag the formula for the entire column

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.

Dataset with date and time in same cell

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

  1. Select cell range A2:A5.
Select the cells that have the date and time
  1. Go to the Developer Tab. In the Code Group, click Visual Basic.
Click on Visual basic
  1. Go to Insert and select Module.
Insert a module
  1. 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
  1. Press F5 on your keyboard to run the code and you should see the following.
Run the VBA code

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.

Dataset with date and time in same cell

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.
Click on Table option in Insert tab
  1. Click Ok to create a Table.
Create Table dialog box
  1.  You should see the following.
Dataset converted to an Excel Table

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.
Click on From Table/Range option
  1.  You should see the Table loaded in the Power Query Editor.
Power Query Editor Opens
  1. Go to the Home Tab. In the Transform Group, click on the Split Column option.
Click on Split column
  1. Choose the By Delimiter option.
Click on  by Delimiter
  1. You should see the Split Column By Delimiter Window.
Split Column by Delimiter window
  1. Choose Space as the Delimiter. For the Split at option, select the Left-most delimiter.
Select space as delimiter
  1. Click Ok. You should see the following.
Result in Power Query
  1. Right-click the Header Date and Time.1 and select Rename…
Rename column headers
  1. Change the Name to Date.
  2. Do likewise for the Date and Time.2 Column and change the name to Time.
Rename time column as well
  1. 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.
click on Close & Load

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: