The date and time value can be written in one cell but can be separated. There are several ways to separate them. For example, there are date and time values as shown below with various formats. How to separate date and time in excel?
Using INT function and subtraction formula
Excel stores date and time in a number. The integer number represents the DATE value, and the decimal number represents TIME value.
For a more detailed explanation, please read “How does Excel Stores Dates and Times“
Use the excel INT function to get integer number that represents DATE, to get the decimal number use the reduction formula.
The Formula for the date value
=INT(A2)
The Formula for the time value
=A2-B2
The Result
Using QUOTIENT and MOD function
In addition to the INT function, the excel QUOTIENT function can be used to get the integer number by using the number 1 as the denominator argument.
To get a decimal number other than using the reduction formula, you can use the excel MOD function by using the number 1 as the divisor argument. Also, the use of the excel MOD function has advantages because it does not depend on the DATE value. If at any time the DATE value is removed (just extracting the TIME value), the TIME value remains unchanged.
The Formula for the date value
=QUOTIENT(A2,1)
The Formula for the time value
=MOD(A2,1)
The Result
Using YEAR, MONTH, DAY, HOUR, MINUTE and SECOND function
If you want to extract the DATE value in more detail to the number of years, month and day, you can use the YEAR, MONTH and DAY function and extract the TIME value to the number of hours, minute and second, you can use the HOUR, MINUTE and SECOND function.
The Formula for year number
=YEAR(A3)
The Formula for month number
=MONTH(A3)
The Formula for day number
=DAY(A3)
The Formula for hour number
=HOUR(A3)
The Formula for minute number
=MINUTE(A3)
The Formula for second number
=SECOND(A3)
The Result
