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

VBA Code to Split Date and Time in Excel Using VBA


Download Practice Workbook

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


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

VBA Code to Split Date and Time in Excel Using VBA

⧭ 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

Entering Input to Split Date and Time Using Excel VBA

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


2 Suitable Examples to Split Date and Time Using Excel VBA

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.

Data Set to Split Date and Time Using Excel VBA

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

VBA Code to Split Date and Time Using Excel VBA

⧭ 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:

=SplitDateandTime(C3)

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.

Entering Function to Split Date and Time Using Excel VBA

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


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. And don’t forget to visit our site ExcelDemy for more posts and updates.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo