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
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.
- 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.
Dim Dataset, j As Long
Dataset = Range("C5:C17")
For j = 5 To 17
With Range("C" & j)
.NumberFormat = "mm-dd-yy"
.Value = DateValue(.Value)
- 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.
Read More: VBA Date to String Conversion in Excel
Things to Remember
- You need to change the range of 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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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.