How to Separate Date and Time in Different Columns

How-to-Separate-Date-and-Time-in-Excel-using-the-DATETIME-function

In this tutorial, you will learn how to separate Date and Time in different columns in Excel using 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.

Steps to Separate Date and Time in Different Columns

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: