How to format date through VBA

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-macros-and-vba/vba-format-date.html
SHARE




In this article, we are going to learn how to do Excel VBA format date in long date.

For Example: – Format (#29/03/2015#, “Long Date”) would return ‘March 29, 2015?

Let’s take an example to understand:-

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 long number date as this format will display the day of week, the month, the day of month and the year.

 

img1

 

We write the macro to change in VBA date format. We will have to follow below given steps:-

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

 

Sub Formatlongdate()

Dim Sh As Worksheet

Set Sh = ThisWorkbook.Sheets(1)

Sh.Range(“C2:C9″).NumberFormat = “[$-F800]dddd, mmmmdd, 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 long date format.

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

 

img2

 

@ www.excelforum one of the users has asked the same question:-

 

Date formatting cells w/ VBA

I am trying to format a cell when creating an Excel spreadsheet from an
access module. I would like to define the format of the cells from the
vb code itself, but I don’t know how exactly I can obtain the desired results.

http://www.excelforum.com/excel-programming-vba-macros/558695-date-formatting-cells-w-vba.html

 

Excel

Downloading – Format Number in Long date through VBA – 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>