Excel VBA to Convert Date and Time to Date Only

It often becomes necessary to convert a range of dates and times to dates only. We can use Excel VBA to convert date and time to date only. This article shows how to do that in the simplest way. The following picture gives an idea of the purpose of this article.

Excel VBA to Convert Date and Time to Date Only


Download Practice Workbook

You can download the practice workbook from the download button below.


Excel VBA to Convert Date and Time to Date Only

Imagine you have the following dataset of dates and times in column B.

Now, select the entire range of dates and times. Then, change the number format to General. After that, you will see the dates and times converted to decimal numbers as the following.

Here, the integer parts of the numbers represent dates. And the decimal fractions represent times.

Now, suppose you want to use Excel VBA to convert the dates and times to dates only. Then, follow the steps below.

📌 Steps

  • At first, you may press CTRL+Z to go back to the date and time format.
  • Then, press ALT+F11 on Windows and Opt+F11 on Mac to open Microsoft Visual Basic for Applications. You can also do that from the Developer tab.
  • Now, insert a new module by selecting Insert >> Module.

Insert Module in Excel VBA to Convert Date and Time to Date Only

  • After that, copy the following code.
Sub DateTimeToDateOnly()
Dim Dataset, j As Long
Range("B5:B17").Copy Range("C5:C17")
Dataset = Range("C5:C17")
For j = 5 To 17
    With Range("C" & j)
        .NumberFormat = "mm-dd-yy"
        .Value = DateValue(.Value)
    End With
Next j
End Sub
  • Then paste the copied on the blank module as follows.

  • Next, run the code by clicking on the Run button or from the Run

  • After that, the dates and times will be converted to dates only as shown below.

Result : Excel VBA to Convert Date and Time to Date Only

Now, select the entire range of dates and times and the converted dates. Then, change the number format to General. You will see that the converted dates contain integers only. This verifies that the dates and times have been properly changed to dates only.

Related Content: How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)


Things to Remember

  • You need to change the range arguments in the code according to your own dataset.
  • You may change the mm-dd-yy format in the code to your desired date format.

Conclusion

Now, you know how to use Excel VBA to convert date and time to date only. If you have further questions or suggestions, please use the comment section below. You can also visit our Exceldemy blog to learn more about excel and improve your performance.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo