VBA to Remove Time from Date in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to remove time from date in excel vba

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 Sub

Your code is now ready to run.

Remove time from date of a column in excel vba

  • 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.

Result of remove time from date of a column in excel vba

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 Sub

Your code is now ready to run.

Remove time from a range of date in excel vba

  • 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.

Selecting range to remove time from date in excel vba

The result is shown in the picture below.

Result of remove time from a range of date in excel vba

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:


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 Sub

Your code is now ready to run.

Remove time from date and place differently in excel vba

  • 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.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo