Split Excel Date 📅 and Time ⏰

Split Date and Time Values

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 example uses a simple formula, and the second example is done without a formula -- just a bit of typing. The written instructions are below the video. The Video Transcript is at the end of this page

Get Date Value With Formula

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, there is a combined date-time in cell B2.

In cell B3, the date has been formatted as a decimal number.

• The integer in part of that number (42418) represents the date
• 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.

Get Time Value With Formula

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.

There are 3 Time formulas below:

     1. Time - Hour, Minute, Second

     2. Time - Hour, Minute

     3. Time - Hour

1. Time - Hour, Minute, Second

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, to get the time with hour, minute and second:
  • =A2 - INT(A2)
• Then, copy the formula down to the last row of data.

To format the times,

• On the Home tab of the Excel Ribbon, click the drop down arrow for Number formatting
• Click on the Time option

Time Formula in Excel Table

If the data is in a named Excel table, the Time 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.

2. Time - Hour, Minute

In some worksheets, you might want the time with hour and minute only, excluding the seconds.

Use this formula in cell D2, where the Date is in cell A2

• =TIME(HOUR(A2),MINUTE(A2),0)

The TIME function has 3 arguments - 1-Hour, 2-Minute, 3-Second

1. HOUR gets the hour from cell A2
2. MINUTE gets the minute from A2.
3. Zero is entered for the Second argument, instead of using the value in A2

To format the time for hour and minute:

• On the Home tab of the Excel Ribbon, at the bottom right of the Number group, click the Dialog Box Launcher (or use the keyboard shortcut, Ctrl + 1 )
• In the Format Cells dialog box, click the Time category
• Click on the 1:30 PM format, then click OK

3. Time - Hour

If you just want to show the hour, you can show that value as a number, or as a time

Hour as Number

To get the hour as a number, use this formula: =HOUR(A2)

Format that result as a Number or as General, to just see the number in the cell.

Hour as Time

To get the hour as time, use this formula: =TIME(HOUR(A2),0,0)

Format that result as a Time, such as 13:30 or 1:30 PM

Get Date or Time Value -No Formula

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.

Get the Sample File

Get the sample Split Date and Time workbook. The zipped file is in xlsx format, and does not contain any macros.

Video Transcript

Here is the transcript for the Split Date and Time video, at the top of this page.

How to Split Date and Time in Excel

If you have a file with dates and times in the same cell, we'll see two quick ways that you can separate that into date and time columns.

Split With Formulas

The first one will use a formula. So in here I'm going to click and type an equal sign.

We'll use the INT function, which is for an integer and that is going to pull the whole number from this cell, which is just the date part of the date and time.

When I press Enter we'll see a date. Now if you see a number, you can format that as a date.

Then I can select that cell, double-click on the Fill Handle, and I very quickly have all the dates.

Get the Time Value

If a date has a time attached to it, that's a decimal number.

So if it's 12 noon, it would be the date number 0.5.

So we want to just get that decimal portion, so we'll type equals the date. So that's the full member including the decimal.

And then minus the date portion, we want to subtract that full number.

So we'll use the INT bracket and click on the date. And when I press Enter, we get the time. And again, you might have to format that as time, if you're just seeing a number. And then fill that down.

Split Without Formulas

We can also pull this out without a formula. So here's another copy of the same list. And in here I'm going to just type the first two dates.

So I'll type 2/1/16 and 2/3/16.

And now we're going to use the Flash Fill. And if you're doing this with text, it would fill down automatically, usually, once we set a pattern, but for numbers or dates, it doesn't do that.

We can go to the Data tab, and click Flash Fill, and then it will suggest the rest of the dates for you.

And then you can come here and accept or undo or just leave it as is if you want to accept it.

Get the Time Value

And we can do the same thing for the time. So I'll type the first couple, and 9:32

And now again, I'll go and click Flash Fill, and it fills down the rest and they look fine to me. So I'll leave those as is.

Now those won't change if these dates change.

On the sheet where we used a formula, if we update a time or the date, because we have formulas here, those would update.