Md. Shamim Reza
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.
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.
- 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.
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.
