How to Split Date and Time Using VBA in Excel – 2 Methods

Quick View:

Sub Split_Date_and_Time()

Date_with_Time = CDate(InputBox("Enter the Date with Time: "))

Separated_Date = Int(Date_with_Time)
Separated_Time = Date_with_Time - Separated_Date
Display_Date = Format(Separated_Date, "dd/mm/yyyy")
Display_Time = Format(Separated_Time, "hh:mm:ss")
Output = "Date: " + Display_Date + vbNewLine + vbNewLine + "Time: " + Display_Time
MsgBox Output

End Sub

Split Date and Time Using Excel VBA 

Step 1: Input Data

• Enter date with time.

You can specify it inside the code, or use an InputBox.

An InputBox was used here.

Date_with_Time = CDate(InputBox("Enter the Date with Time: "))

Step 2: Separating the Date

The date is the integer, and time is the fractional part of the number.

• Separate the date using the Int function of VBA:

Separated_Date = Int(Date_with_Time)

Step 3: Separating the Time

• Subtract the date from the initial input: date with time.

Separated_Time = Date_with_Time - Separated_Date

Step 4 (Optional): Displaying the Outputs

• Use a Message Box to display date and time (the Format function and the vbNewLine object were used).

Display_Date = Format(Separated_Date, "dd/mm/yyyy")
Display_Time = Format(Separated_Time, "hh:mm:ss")
Output = "Date: " + Display_Date + vbNewLine + vbNewLine + "Time: " + Display_Time
MsgBox Output

VBA Code:

Sub Split_Date_and_Time()

Date_with_Time = CDate(InputBox("Enter the Date with Time: "))

Separated_Date = Int(Date_with_Time)

Separated_Time = Date_with_Time - Separated_Date

Display_Date = Format(Separated_Date, "dd/mm/yyyy")
Display_Time = Format(Separated_Time, "hh:mm:ss")
Output = "Date: " + Display_Date + vbNewLine + vbNewLine + "Time: " + Display_Time
MsgBox Output

End Sub

Output:

• Run the code.
• Enter the date with time in the InputBox. Here, 3/9/2021 5:17:09 AM

• Click OK.

You’ll see the split date and time in two lines in a Message Box.

Example 1 – Developing a Macro to Split Date and Time Using Excel VBA

The sample dataset contains the candidates’ names and their interview schedule.

To split the Interview Schedule into date and time:

• The VBA code will be:

Sub Split_Date_and_Time_Range()

Set Input_Range = Range("C3:C12")
Set Output_Range = Range("D3:E12")

For i = 1 To Input_Range.Rows.Count
    Date_with_Time = Input_Range.Cells(i, 1)
    Separated_Date = Int(Date_with_Time)
    Separated_Time = Date_with_Time - Separated_Date
    Display_Date = Format(Separated_Date, "mm/dd/yyyy")
    Display_Time = Format(Separated_Time, "hh:mm:ss")
    Output_Range.Cells(i, 1) = Display_Date
    Output_Range.Cells(i, 2) = Display_Time
Next i

End Sub

 Output:

• Run the code.

It will return Date and Time.

Example 2 – Creating a User-Defined Function to Split Date and Time Using Excel VBA

• The VBA code will be:

Function SplitDateandTime(Date_with_Time)

Dim Output As Variant
ReDim Output(0, 1)

Separated_Date = Int(Date_with_Time)
Separated_Time = Date_with_Time - Separated_Date

Display_Date = Format(Separated_Date, "mm/dd/yyyy")
Display_Time = Format(Separated_Time, "hh:mm:ss")

Output(0, 0) = Display_Date
Output(0, 1) = Display_Time

SplitDateandTime = Output

End Function

Output:

The code creates the SplitDateandTime function.

• Select two cells in your worksheet and enter the formula:

• Press ENTER or CTRL + SHIFT + ENTER (for older Excel versions).

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

Download Practice Workbook

Download the practice workbook.

<< Go Back to Date and Time | Split | Learn Excel