If you download data into Excel, one column might have a combined date and time value. If you’re going to create reports from that data, it’s usually easier if you have the date and times in separate columns. See how you can do that with a simple formula, or a quick bit of typing.
Split Date and Time
In Excel, dates are stored as numbers, as you can see in the screen shot below. The integer in cell B3 (42418) represents the date, and the decimal portion (.50) is the time.
Use the INT Function
A simple way to split the date and times is with the INT function. Because it returns an integer, the INT function can calculate the date from a date/time combination.
If the date/time data is in cell A2, use this formula to get the date (the integer): =INT(A2)
NOTE: After you enter the formulas, format those columns with your preferred Date and Time formats.
Get the Time Value
To calculate the time value, we’ll 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. To calculate the time, enter the following formula in cell C2:
=A2 – INT(A2])
Use Flash Fill
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. However, these values 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.
- On the Excel Ribbon, click the Data tab on the Excel Ribbon
- Click the Flash Fill command
The rest of the dates should fill in, and you can use the Flash Fill options box to confirm or undo the changes.
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 File
To get the sample file, go to the Split Date and Time page on my Contextures website. There is a link in the Download the Sample File section. The zipped file is in xlsx format, and does not contain macros.
Watch the Video
To see the steps for splitting the date and time, watch this short video tutorial. You’ll see both methods – the formula, and the Flash Fill.
______________________________
