Excel VBA: Combine Date and Time (3 Methods)

While making a time log of activities in Excel, you may want to combine date and time. In most cases, you may have dates in one column and time in another column in your worksheet. But Excel has some features and functions with which you can easily combine date and time values.  We also can do that by applying VBA Macros. Today, in this article, we’ll learn three quick and suitable ways to combine date and time by applying the VBA code in Excel effectively.


VBA to Combine Date and Time (Quick View)

Sub Combine_Date_and_Time()
Dim R As Worksheet
Set R = Worksheets("Date and Time")
'combine date and time into one cell
R.Range("E5") = Application.WorksheetFunction.Text(R.Range("C5"), "dd/mm/yyyy") & " " & Application.WorksheetFunction.Text(R.Range("D5"), "hh:mm:ss")
End Sub


Download Practice Workbook

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


3 Suitable Ways to Combine Date and Time Using VBA in Excel

Let’s say, we have a dataset that contains information about several Sales representatives of the Armani group. The Joining Date and Time of the Sales representatives are given in columns C, and D respectively. We will combine these employees’ joining date and time in column E by applying a simple VBA Code and creating a User Defined Function also. Here’s an overview of the dataset for today’s task.


1. Use of Mathematical Operator to Combine Date and Time in Excel VBA

In this method, we will combine date and time in Excel by applying the VBA Code. Applying the VBA code to combine time and date is the easiest and the time-saving also. Let’s follow the instructions below to learn!

Step 1:

  • First of all, from your Developer tab, go to,

Developer → Visual Basic

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Apply a Plus(+) Sign with VBA Code to Combine Date and Time in Excel

Step 2:

  • Hence, instantly the Combine Time and Date module will appear in front of you. In the Combine Time and Date module, write down the below VBA code.
Sub Combine_Time_and_Date()
Range("D5").Value = Range("B5").Value + Range("C5").Value
End Sub

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • After running the code, further, go back to your worksheet, and you will be able to combine the date and time that has been given below screenshot.

Apply a Plus(+) Sign with VBA Code to Combine Date and Time in Excel

  • Similarly, you can combine the date and time of the rest of the employees.

Apply a Plus(+) Sign with VBA Code to Combine Date and Time in Excel

Read More: How to Combine Name and Date in Excel (7 Methods)


2. Applying TEXT Function in VBA Code to Combine Date and Time in Excel

In this method, we will learn how to combine the joining date and time of the Sales representatives of the Armani Group by using VBA code. Please follow the steps below to learn!

Step 1:

  • According to method 1, insert a new module and type the below VBA code to merge date and time. The VBA code is,
Sub Combine_date_and_time()
Dim R As Worksheet
Set R = Worksheets("Date and Time")
'combine date and time into one cell
R.Range("E5") = Application.WorksheetFunction.Text(R.Range("C5"), "dd/mm/yyyy") & " " & Application.WorksheetFunction.Text(R.Range("D5"), "hh:mm:ss")
End Sub

Use a VBA Code to Combine Date and Time in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the code, go back to your worksheet, and you will be able to combine the date and time that has been given below screenshot.

Use a VBA Code to Combine Date and Time in Excel

  • Similarly, you can merge the date and time of the rest of the employees by applying the VBA code.

Use a VBA Code to Combine Date and Time in Excel

Read More: How to Combine Data from Multiple Sheets in Excel (4 Ways)


Similar Readings


3. Create a User Defined Function to Combine Date and Time in Excel

Now, we will create a user-defined function to combine the joining date and time of the Sales representatives of the Armani Group by using VBA code. Let’s follow the instructions below to learn!

Step 1:

  • According to method 1, insert a new module and type the below VBA code to merge date and time. The VBA code is,
Function CombineDate(DateR As Range)
    Dim DateS As String: DateS = DateRng.Value
    Dim yy As String, mm As String, dd As String
    yy = Left(DateS, 4)
    mm = Mid(DateS, 5, 2)
    dd = Right(DateS, 2)
    CombineDate = Format(DateSerial(yy, mm, dd), "m/dd/yyyy")
End Function
Function CombineTime(TimeR As Range)
    Dim TimeS As String: TimeS = TimeRng.Value
    Dim hh As String, mn As String, ss As String
    If Len(TimeS) = 5 Then TimeS = "0" & TimeS
    hh = Left(TimeS, 2)
    mn = Mid(TimeS, 3, 2)
    ss = Right(TimeS, 2)
    CombineTime = Format(TimeSerial(hh, mn, ss), "hh:mm:ss")
End Function
Function CombineDateTime(DateR As Range, TimeR As Range)
    Application.Volatile
    CombineDateTime = CDate(CombineDate(DateRng)) + CDate(CombineTime(TimeRng))
End Function

Create a User Defined Function to Combine Date and Time in Excel

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the VBA code, we will be able to create a user-defined function named Now, we will convert the joining date and joining time into Number format. To do that, from your Home tab, go to,

Home → Number → Number

Create a User Defined Function to Combine Date and Time in Excel

Step 3:

  • To apply the user-defined function(UDF) named CombineDateTime, firstly select cell E5 and write down the below The UDF is,
=CombineDateTime(C5,D5)

  • Hence, simply press ENTER on your keyboard and you will get 21/09/19 9:30 AM as the output of the function.

Create a User Defined Function to Combine Date and Time in Excel

  • Now, place your cursor bottom-right of cell E5, and an autoFill handle pops up and drag the autoFill handle downward.

Create a User Defined Function to Combine Date and Time in Excel

  • After completing the above process, you will be able to combine the joining date and joining time of the employees of the Armani Group that has been given below screenshot.

Create a User Defined Function to Combine Date and Time in Excel


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon


Conclusion

I hope all of the suitable methods mentioned above to combine date and time with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo