How to Split Date and Time Using VBA in Excel (2 Easy Methods)

In this article, I’ll show you how you split date and time using VBA in Excel.

Excel VBA: Split Date and Time (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

An Overview to Split Date and Time Using Excel VBA (Step-by-Step Analysis)

Without further delay, let’s go to our main discussion today. Let’s see how we can split a given date with time into separate dates and separate times with step-by-step analysis.

⧪ Step 1: Inserting the Input

First of all, we have to insert the inputs into the code. There is only one input required in this code: the given date with time.

You can specify it inside the code, or you can use an InputBox to let the user input it each time the code is run.

I’ve used an InputBox here.

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

⧪ Step 2: Separating the Date

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

Therefore, next, we’ll separate the date using the Int function of VBA.

Separated_Date = Int(Date_with_Time)

⧪ Step 3: Separating the Time

Then we’ll separate the time from the date. We’ll accomplish it by subtracting the separated date from the input date with time.

Separated_Time = Date_with_Time - Separated_Date

⧪ Step 4 (Optional): Displaying the Outputs

The date and time have been separated already. Finally, we’ll use a Message Box to display the separated date and time. We’ll use the Format function of VBA and the vbNewLine object for the sake of visualization.

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

Therefore, the complete VBA code will be:

⧭ 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. First, it’ll ask you to enter the date with time in an InputBox.

I’ve entered a random date here, 3/9/2021 5:17:09 AM

Click OK and you’ll get the split date and time separately in two lines in a Message Box.

How to Split Date and Time Using Excel VBA: 2 Suitable Examples

We’ve learned how to split dates with times into separate dates and separate times using Excel VBA. Now we’ll explore a few examples of this.

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

Here we’ve got a data set extending over the range B3:C12 that contains the names of some candidates and their interview schedules for a company.

Our objective is to develop a Macro to split the Interview Schedules into dates and times in the range D3:E12.

The VBA code will be:

⧭ VBA Code:

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 (Obviously after changing the inputs). It’ll split the Interview Schedules into Dates and Times in range D3:E12.

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

Now, we’ll develop a User-Defined function to split the dates into dates and times.

The VBA code will be:

⧭ VBA Code:

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 a function called SplitDateandTime.

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

Then press CTRL + SHIFT + ENTER (Not necessary if you are in Office 365).

It’ll split the Interview Schedule of cell C3 into Date and Time separately.

Then you can drag the Fill Handle to repeat the same for the rest of the schedules.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

So, this is the way to split a given date with time into a separate date and time using VBA in Excel. Do you have any questions? Feel free to ask us.

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