WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Format Date

VBA Format Date

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA Format Date

To format a date in VBA we use the inbuilt FORMAT function itself, it takes input as the date format and returns the desired format required, the arguments required for this function are the expression itself and the format type.

Formatting of date & time are sensitive things in excel, and the same thing applied to VBA as well. The default date and time is based on the system date on which we are working, which may vary from system to system. In this article, we will show you different techniques for formatting dates with VBA codes.

To change the date format with VBA coding, we need to be aware of what the date formats are and its impact on the date.

The below table shows the different date formatting and their codes.

VBA Format Date - Format

So, if you keep in mind the above chart formatting the date through VBA coding isn’t a tough task at all.

How to Change Date Format in VBA?

Below are the examples of excel VBA date format.

You can download this VBA Format Date Excel Template here – VBA Format Date Excel Template

Example #1

For example, we have the same date in multiple cells of the worksheet, as shown below.

VBA Format Date Example 1.0

Now we will apply different date formats for the same date to see the impact at different date format codes.

First, copy the same data to the next column as well to see the impact.

Example 1.1

For the first date i.e., cell A1 we will apply the “DD-MM-YYYY” format.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA Format Date Example 1.2.0

In the code first, we need to select the cell by using the RANGE object.

Code:

Sub Date_Format_Example1()

   Range ("A1")

End Sub

VBA Format Date Example 1.3

Since we are changing the date format of the cell, we need to access the “Number Format” property of the RANGE object.

Code:

Sub Date_Format_Example1()

  Range("A1").NumberFormat

End Sub

Example 1.4

After accessing “Number Format,” we need to set the number format by putting the equal sign and apply the format code in double-quotes.

Code:

Sub Date_Format_Example1()

   Range("A1").NumberFormat = "dd-mm-yyy"
   'This will chnage the date to "23-10-2019"

End Sub

VBA Format Date Example 1.5

When we execute this code, it will apply the number format to the cell A1 as “DD-MM-YYY.”

Output:

Example 1.6

Example #2

Similarly, I have applied different formatting codes for other cells as well, and below is the VBA code for you.

Code:

Sub Date_Format_Example2()

   Range("A1").NumberFormat = "dd-mm-yyy"
   'This will change the date to "23-10-2019"

   Range("A2").NumberFormat = "ddd-mm-yyy"
   'This will change the date to "Wed-10-2019"

   Range("A3").NumberFormat = "dddd-mm-yyy"
   'This will change the date to "Wednesday-10-2019"

   Range("A4").NumberFormat = "dd-mmm-yyy"
   'This will change the date to "23-Oct-2019"

   Range("A5").NumberFormat = "dd-mmmm-yyy"
   'This will change the date to "23-October-2019"

   Range("A6").NumberFormat = "dd-mm-yy"
   'This will change the date to "23-10-19"

   Range("A7").NumberFormat = "ddd mmm yyyy"
   'This will change the date to "Wed Oct 2019"

   Range("A8").NumberFormat = "dddd mmmm yyyy"
   'This will change the date to "Wednesday October 2019"

End Sub

VBA Format Date Example 1.7

The result of this code will be as follows.

Output:

Example 1.8

Change Date Format by Using FORMAT Function

In VBA, we have a function called FORMAT, which can be used to apply the desired format to the cell.

VBA Format Date Syntax

We just need to specify what the value is for “Expression” and apply the “Format” accordingly.

Look at the below code for an example.

Code:

Sub Date_Format_Example3()

  Dim MyVal As Variant

  MyVal = 43586

  MsgBox Format(MyVal, "DD-MM-YYYY")

End Sub

VBA Format Date Example 1.9

In the above code, I have defined the variable as variant (which can hold any value).

Code:

Dim MyVal As Variant

Next for this variable, I have assigned the value as 43586.

Code:

MyVal = 43586

Next, in the message box, I have shown the result of the variable, but before we show the result, we have used the “FORMAT” function to format the value of the variable “MyVal,” and the format is given. “DD-MM-YYYY.”

Code:

MsgBox Format(MyVal, "DD-MM-YYY")

Ok, let’s run the code and see the result in the message box in VBA.

Output:

Example 1.10

As you can see above, the result shows as “01-05-2019”.

Now you must be wondering we have supplied the serial number but the result showing as the date. This is because Excel stores the date as serial numbers, so the value 43586 is equal to the date “01-05-2019,” and if you increase the number by 1 i.e., 43587, the date will be “02-05-2019”.

Code:

Sub Date_Format_Example3()

  Dim MyVal As Variant

  MyVal = 43586

  MsgBox Format(MyVal, "DD-MM-YYY")

End Sub

VBA Format Date Example 1.11

Things to Remember

  • The default date of your system will be applied to your excel as well.
  • Number Format property can be used to change the date format in VBA.
  • Using the FORMAT function, we can change the date format.
  • Excel stores date as serial numbers, and if you apply date format, it will show accordingly.

Recommended Articles

This has been a guide to VBA Format Date. Here we discuss how to change the date format in excel VBA using different formatting codes and Format Function along with examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • Copy Formatting in Excel
  • Subtract Date In Excel
  • CDATE Function in VBA
  • Range Variable in VBA
  • VBA RGB
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Format Date Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More