How to Separate Date and Time in Different Columns

Steps to Separate Date and Time in Different Columns

In this tutorial, you will learn how to separate Date and Time in different columns in Excel using the INT function.

Steps to Separate Date and Time in Different Columns

Steps to separate date and time into two different columns in Excel:

  • Type =INT(B3)
  • The formula returns with the date part of the cell.
  • Type =B3-INT(B3)
  • The formula returns with the time part of the cell.

Explanation

Take a closer look at the formula. The INT function removes the decimal portion of a number, effectively truncating any fraction. This is particularly useful when dealing with date and time data, as Excel stores dates as integers (representing days since January 1, 1900) and times as decimal fractions (representing fractions of a day).

Steps to Separate Date and Time in Different Columns

For example, if you have a date-time value like 3/15/2024 14:34:11, Excel internally stores it as a number (e.g., 45198.60707).

  • The integer part (45198) represents the date (days since January 1, 1900).
  • The decimal part (0.60707) represents the time (fraction of a day).
extract the time part from datetime

Formula in D3:

=INT(B3)

This formula extracts only the date part from the combined DateTime by removing the decimal (time) part.

Formula in F3:

=B3 – INT(B3)

This formula extracts the time part by subtracting the integer (date) part, leaving only the decimal (time).

The DATETIME function

The DATETIME function is designed to extract either the date or the time part from a given cell containing a date and time value in a cell.

Let’s see the steps to split the date and time:

  1. Type =DATETIME(A1, 1)
  2. The formula returns with the date part of the cell.
  3. Type =DATETIME(A1, 2)
  4. The formula returns with the time part of the cell.

How the DATETIME function works

DATETIME is a user-defined function and a part of our free function library. In this section, we’ll briefly overview how to use it.

The function uses two required arguments to separate date and time:

Function DATETIME(cellRef As Range, ReturnType As Integer) As Variant
  • cellRef is a range. This refers to the cell containing the datetime value.
  • returnType is an integer type variable that specifies what part of the DateTime to return. If it’s 1, it returns the date part; if it’s 2, it returns the time part.
Dim dt As Variant
dt = cellRef.Value

Here, we declare a variant ‘dt‘ to store the cell reference value passed to the function.

' Check if the cell value is of date/time type
If IsDate(dt) Then

This line checks if the value ‘dt‘ is a valid date/time value.

Select Case ReturnType
    Case 1
        DATETIME = Int(dt) ' Date part only
    Case 2
        DATETIME = dt - Int(dt) ' Time part only
    Case Else
        DATETIME = "Invalid 'returnType' value. Please use 1 for date or 2 for time."
End Select

If the value in ‘dt’ is a valid date/time value, the function checks the value of ReturnType. In the case of 1, it extracts the date part by taking the integer part of ‘dt’.

If ReturnType is 2, it extracts the time part by subtracting the integer part of dt from dt.

Else
    DATETIME = "Invalid data type!"
End If

If the value in ‘dt’ is not a valid date/time value, the function returns an error message. Therefore, we strongly recommend using custom functions to separate dates and times in Excel easily.

More resources: