How we can format date through VBA?

In this article we will learn how to format the date through VBA.

Let’s take an example to understand how and where we can format the cell in short date number.

We have product’s amount data in range A1:C9 in which column A contains Product name and column B contains amount of it and column C contains the date. Now, we want to convert into short date format.

To write the macro, follow below given steps:-

Open VBA Page press the key Alt+F11.
Insert a module.
Write the below mentioned code:

Sub FormatShortdate()

Dim Sh As Worksheet

Set Sh = ThisWorkbook.Sheets(1)

Sh.Range("C2:C9").NumberFormat = "m/d/yyyy"

End Sub

Code Explanation:-

First, we have given the subject for the code, then we had defined all the variables after that we had defined the range where we want to put the short date format.

To run the macro, press the key F5, and the range format will be changed into short date format.

Let’s take an example from www.excelforum.com

Macro/VBA to change date format from 01.01.2011 to 01/01/2011

Original Question:-

I have an Excel web query that is pulling off a table into Excel. Column A is basically a list of names and column B is the relevant date. The dates are listed on the website as 01.01.2011 format and are subsequently imported into Excel this way. As a result, I cannot sort column B by date value as Excel is not recognizing the format. I’ve tried changing the properties of the date, using find & replace to change it to 01/01/2011 format then changing properties all to no avail.

Ideally I’d just like some sort of Macro/VBA code to change 01.01.2011 into 01/01/2011 format so that I can sort the column.

To know about the solution, please click on link