When your data contains date and time mutually in one cell, you can easily split them into two separate values as a date and time value. You will get a thorough understanding of how to separate date and time in Excel with some useful methods.
Let’s see how you can do it:
Below are some useful formulas to Separate Date
=INT(A1)
=ROUNDDOWN(A1,0)
=TRUNC(A1)
Below are some useful formulas to Separate Time
=A1-INT(A1)
A1 cells given in the above formulas replace the date and time value
Overview of Date and Time in Excel:
In Excel, the time value is stored as a fraction, and the date value is stored as a numeric. Did you know that?
Suppose, you have a date value in cell A1 which is 44541, and the time value is given as 0.52.
On the other hand, in cell A3, you can see the combined values of time and date as 44541.52.
As you can see the above value has an integer as well as a fraction part that you need to split in order to separate the date and time in Excel.
How to Separate Date and Time in Excel with INT Function?
In this section, you will learn how easily you can separate dates and times in Excel. The dataset uses here has Columns C and D in which you need to separate the time and date values. With the help of the INT function, you can easily separate the date and time. Follow the steps for this:
- Choose the cell range C5:C11.
- Now, apply the Short Date format to this cell range.
- Below given formula is to add in cell C5:
=INT(B5)
- With the INT function, you will notice the number gets rounded down to the nearest integer.
- Press ENTER key and use the Fill Handle icon.
- Now, you will find the date value in Column C.
- Put the following formula in cell D5:
=B5-C5
- By using the above formula, you will get the time value in Column D.
- Press ENTER key and use the Fill Handle.
This is how you can easily separate the date and time values.
How to Separate Date and Time in Excel with Flash Fill Feature?
In this method, you have a dataset in which a list of dates and times is given in a single column.
You can use the Flash Fill feature to split the date and time values. For this, follow the steps given below:
- Add the date value from cell A2 to cell B2.
- Add the date value from cell A3 to cell B3.
- Choose cell B4.
- Open the Data tab.
- Click on the Flash Fill option given in the Data Tools menu.
- Add the time value from A2 in cell C2.
- Add the time value from cell A3 to cell C3.
- Choose cell C4.
- Open the Data tab.
- Click on the Flash Fill option given in the Data Tools menu.
- Excel by default will recognize the patterns if the Flash Fill feature is activated. In Column B, you can split the date value from the adjacent cell. And the date value is that’s why added for the first two cells. That’s it!
How to Separate Date and Time with Keyboard Shortcut?
Keyboard shortcuts are the most wanted solution for the majority of Excel users. The fastness and superb results of shortcut commands do not have a match. With keyboard shortcuts, you can easily separate the date and time values. Let’s see how you can do it:
- Add the first two dates in columns C5 and C6.
- Press CTRL + E from the keyboard.
- You will ultimately get the date value in Column C.
- Once again add the first two values of time in columns D5 and D6.
- Press CTRL + E from the keyboard.
This is how the date and time values are separated easily.
How to Separate Date and Time Values with VBA?
VBA is another great option to avail when you need to split the date and time values. Here the dataset contains a list of time and date values in a single column.
Follow the steps given here:
- Choose cell range A2:A5.
- Open the Developer tab.
- Click on the Visual Basic option from the Code menu.
- Open the Insert tab and choose Module.
- Now, simply copy and paste the below-given code in the module.
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
- Press F5 from the keyboard. It will run the code that you have pasted in the module.
That’s it!
Summary:
In this post, you have learned how to separate date and time in Excel with useful methods. Each method has its worth and all the above tricks are highly recommended.
