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
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.
- Similarly, you can combine the date and time of the rest of the employees.
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
- 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.
- Similarly, you can merge the date and time of the rest of the employees by applying the VBA code.
Read More: How to Combine Data from Multiple Sheets in Excel (4 Ways)
Similar Readings
- How to Combine Two Scatter Plots in Excel (Step by Step Analysis)
- How to Combine Rows from Multiple Sheets in Excel (4 Easy Methods)
- Combine Multiple Excel Files into One Workbook with Separate Sheets
- How to Combine Multiple Excel Files into One Worksheet Using Macro
- How to Combine Two Bar Graphs in Excel (5 Ways)
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
- 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
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.
- Now, place your cursor bottom-right of cell E5, and an autoFill handle pops up and drag the autoFill handle downward.
- 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.
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
- How to Merge Columns in Excel (4 Ways)
- Combine Columns into One List in Excel (4 Easy Ways)
- How to Combine Rows in Excel (6 Methods)
- Combine Sheets in Excel (6 Easiest Ways)
- How to Merge Multiple Excel Files into One Sheet (4 Methods)
- How to Consolidate Data in Excel from Multiple Worksheets (3 Ways)
