VBA Format Date

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.

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

How to Change Date Format in VBA?

Below are the examples of excel VBA date format.

Example #1

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

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.

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

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

Code:

Sub Date_Format_Example1()

   Range ("A1")

End Sub

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

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

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

Output:

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

The result of this code will be as follows.

Output:

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.

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

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 is “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:

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

Now you must be wondering we have supplied serial number but result showing as the date. This is because Excel stores 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

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