In Excel, if you have a date and time combined in a single cell, you can separate it into two different values as a date and time. In this tutorial, we will look at some of the simple and useful methods for this.
Quick Formulas to Separate Date
- =INT(A1)
- =ROUNDDOWN(A1,0)
- =TRUNC(A1)
Quick Formulas to Separate Time
- =A1-INT(A1)
In the above formulas, A1 is the cell where you have the date and time.
Understanding Date and Time in Excel
Excel stores a date as a numeric value and time as a fraction.
And in the following example, you can see for the date in cell A1 you have the numeric value 44541, for the time in cell A2 it’s 0.52, and in cell A3 you have a date and time combined in the cells and numeric value 44541.52.
So, if you want to Separate date and time into two separate values you need to Separate the integer part and the fractional part from the value.
Separate Date from Time in Excel
One of the best ways to Separate a date from a date and time value is by using the INT function. INT function takes an integer from a number and left out the fractional part.
=INT(A1)
In the above example, you have the INT function that takes the reference from cell A1 where you have the date and time in a numeric value. And, it has returned 44541 in the result.
And now when you format this as valuable as a date it only shows you the date. Now, apart from the above methods, you can use the following two functions as well to get a separate date.
Using the ROUNDDOWN function to remove the time from and split the date.
=ROUNDDOWN(A1,0)And you can also use the TRUNC function to trim the decimal to separate the date.
=TRUNC(A1)
Separate Time from Date in Excel
To separate time, you can use a formula using the INT function. In the following example, you have deducted the integer part from the actual number, and the value that is left is the time.
=A1-INT(A1)