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

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




Download - VBA Short Date Format - xlsm

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.