If a cell contains the combined date and time, use a formula to extra the date or time in separate columns. Or, use a non-formula method.

If you download data into Excel, one column might have a combined date and time value. In this short video, see how to quickly split that value into separate date and time columns. The first method uses a simple formula, and the second method is done without a formula -- just a bit of typing. The written instructions are below the video.

If a cell contains a combined date and time, you can extract just the date value, by using the INT function. Excel stores dates as numbers, with a decimal portion representing the time.

In the screen shot below, the integer in cell B3 (42418) represents the date, and the decimal portion (.50) is the time.

The INT function returns just the integer portion of that number, which represents the date. In the next example, the combined date/time is in cell A2.

To get the date, enter the following formula in cell B2: **=INT(A2)**

If the data is in a named Excel table, the formula will use structured references with the field names. In the example below, the combined date/time is in the DateTime column. Enter the following formula in the first row of the Date column (cell B2):

Date: : **=INT([@DateTime])**

The formula should automatically fill down to the last row in the table. After you enter the formulas, format those columns with your preferred Date and Time formats.

If a cell contains a combined date and time, you can use the INT function to pull the time value into a separate column. Dates are stored as numbers in Excel, with the decimal portion representing the time.

To calculate the time value, subtract the date integer value from the combined date and time. The remaining decimal portion is the time. In the example below, the combined date/time is in cell A2. Enter the following formula in cell C2:

Time: **=A2 - INT(A2])**

If the data is in a named Excel table, the formula will use structured references with the field names. In the example below, the combined date/time is in the DateTime column. Enter the following formula in the first row of the Time column (cell C2):

Time: **=[@DateTime]-INT([@DateTime])**

The formula should automatically fill down to the last row in the table. After you enter the formulas, format those columns with your preferred Date and Time formats.

If you're using Excel 2013 or later, you can use the Flash Fill feature to get the date or time in a separate column, without formulas.

**NOTE**: the dates and times entered with Flash Fill will NOT update
automatically if the combined date/time cell changes -- these are static
entries.

Here's how you can use Flash Fill for dates or times.

- Type the first two dates in column B, based on the dates in column A.

- Then, go to the Data tab on the Excel Ribbon, and click Flash Fill.

The rest of the dates should fill in, and you can use the Flash Fill options box to confirm or undo the changes.

NOTE: If Flash Fill doesn't work, click the File tab at the top of Excel, then click Options. Click the Advanced category, and in the Editing options, add a check mark to "Automatically Flash Fill"

Do the same thing to fill in the Time column -- enter the first two times, then use the Flash Fill feature to complete the list.

Download the sample Split Date and Time workbook

For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Last updated: June 20, 2017 10:29 AM