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

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.

Remove Time from Date with VBA in Excel: 3 Methods

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 Sub

Your 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: Date Variable in VBA Codes

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.

• 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: Excel VBA Textbox to Format Date

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.

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

Download Practice Workbook

You can download the free practice Excel workbook from here.

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.