Table of Contents
The process of separating date and time in Excel involves using the built-in functions and formatting options available. This can be done by selecting the cell containing the date and time information and then using the “Format Cells” option to specify the desired date and time format. Additionally, the “Text to Columns” feature can be utilized to separate the date and time into different columns. This allows for better organization and analysis of the data in Excel.
Separate Date and Time in Excel (With Example)
Often you may want to separate the date and time in a cell in Excel into two cells:
Fortunately this is easy to do and the following example shows how to do so.
Example: Separate Date and Time in Excel
Suppose we have the following column of datetimes in Excel:
Suppose we would like to display the date and time in each cell in two separate cells.
To extract just the date from the first cell, we can type the following formula into cell B2:
=INT(A2)
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the date from each datetime in column A.
The reason INT is able to extract just the date is because Excel stores dates as numeric values that represent the number of days since 1/1/1900 and 1/1/2023 4:15AM is stored as 44927.177.
The INT function extracts just 44927. When displayed as a date, this is shown as 1/1/2023.
Next, type the following formula into cell C2 to extract just the time from cell A2:
=A2-B2
We can then click and drag this formula down to each remaining cell in column B:
By default, the time values will be shown as decimals.
To display the decimals as time, highlight the range C2:C10, then click the Number Format icon on the Home tab, then click Time from the dropdown menu:
The values in column C will now be formatted as time values:
We have now successfully separated the date and time values in column A into two separate columns.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: