Excel VBA to Convert Date and Time to Date Only

Get FREE Advanced Excel Exercises with Solutions!

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


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.

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.


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo