How we can format date through VBA?

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/date-time-in-vba/vba-date-format.html
SHARE




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.
img1

 

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.
img2

 

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

 

 

xlsx-1567

Download – VBA Short Date Format – xlsm

Please follow and like us:
0


Leave a Reply

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

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>