Sanjida Ahmed
Sometimes there are data in an Excel worksheet that consists of both time and date. Occasionally, we want to get rid of only time or date from that sheet. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 3 easy and effective ways how to remove time from date in Excel with the VBA macro.
Download Workbook
You can download the free practice Excel workbook from here.
3 Methods to Remove Time from Date with VBA in Excel
In this section, you will learn how to remove times from dates from a column, how to remove times from a range of dates and how to separate dates from time with VBA in Excel.
The above image is the dataset that this article will use to show you the methods.
1. Embed VBA to Delete Times from Dates from a Column in Excel
From the given dataset above, we will remove the times that are along with the dates in Column D.
The steps to do that are given below.
Steps:
- In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Next, in the pop-up code window, from the menu bar, click Insert -> Module.
- Then, copy the following code and paste it into the code window.
Option Explicit
Sub DeleteTime()
Dim iRange As Long
Dim iCount As Long
iRange = Range("D" & Rows.Count).End(xlUp).Row 'column D has the date values
   For iCount = 5 To iRange 'from row number 5 our date values start
       With Range("D" & iCount)
           .NumberFormat = "dd/mm/yyyy"
           .Value = DateValue(.Value)
       End With
   Next iCount
End SubYour code is now ready to run.
- Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.
After the code execution, look at the image below to see the result.
As a result of the code, the times from the D Column of the dataset have been removed leaving only the date values.
Read More: Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
2. Apply VBA Macro to Remove Time from a Range of Dates
If you want to delete times from a user-defined range of dates instead of a whole column, then follow the steps shown below.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Then, in the code window, copy the following code and paste it.
Option Explicit
Sub RemoveTime()
Dim iRange As Range
Dim iData As Range
On Error Resume Next
Set iData = Application.Selection
Set iData = Application.InputBox("Select Range", "Excel", iData.Address, Type:=8)
For Each iRange In iData
   iRange.Value = VBA.DateValue(iRange.Value)
Next
iData.NumberFormat = "dd/mm/yyyy"
End SubYour code is now ready to run.
- Later, Run the macro.
- A pop-up box will come up asking for the range. Drag through the whole range that you want to strip the time from (in our case, it is Cell D5 to D9)
- Then, press OK.
The result is shown in the picture below.
Finally, we can successfully get rid of the times from the range D5:D9 of our dataset.
Read More: How to Use Formula to Change Date Format in Excel (5 Methods)
Similar Readings:
- How to Convert Date to Year in Excel (3 Quick Ways)
- Convert Date to Day of Week in Excel (8 Methods)
- How to Convert Date to Day in Excel (7 Quick Ways)
- Excel Formula for Current Month and Year (3 Examples)
- How to Convert Date to Month and Year in Excel (4 Ways)
3. Implement Macro to Separate Date from Time in Excel
Consider the following image.
What we are going to show you in this section is, we will separate the dates from Column D and place those in the relevant cells of Column F.
Let’s see how to do that with VBA Excel.
Steps:
- As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Then, in the code window, copy the following code and paste it.
Option Explicit
Sub SeparateDate()
   Dim iCount As Integer
   With Worksheets("Separate")
       'to check if D5 is in date format or in text format
       iCount = Abs(Not IsDate(.Cells(5, 4).Value))
       With .Range(.Cells(5 + iCount, "D"), .Cells(.Rows.Count, "D").End(xlUp))
           .TextToColumns Destination:=.Cells(1, "C"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, xlDMYFormat), Array(10, xlSkipColumn))
           'to assign a custom number format (optional)
           .Offset(0, 2).NumberFormat = "[color5]dd-mm-yyyy_)"
           'to autofit the column width (optional)
           .Offset(0, 2).EntireColumn.AutoFit
       End With
   End With
End SubYour code is now ready to run.
- Now, Run the macro and look at the following image to see the output.
Only the dates from Column D are transferred in Column F successfully.
Related Content: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
Conclusion
To conclude, this article showed you 3 easy and effective methods on how to remove time from date in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.
