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 and the format type.

Formatting date and time are sensitive things in Excel, and the same applies to VBA. The default date and time are based on the system date we are working, which may vary from system to system. This article will show you different techniques for formatting dates with VBA codes.

To change the date format with VBA coding, we need to know the date formats and their impact on the date.

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

So, remember that the above chart formatting the date through VBA coding is not a tough task.

Below are examples of Excel VBA date format.

Example #1

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 to see the impact.

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

We first must select the cell in the code 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 applying 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 cell A1 as “DD-MM-YYYY.”

Output:

Example #2

Similarly, we have also applied different formatting codes for other cells. 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:

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

We need to specify the value 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, we have defined the variable as a variant (which can hold any value).

Code:

Dim MyVal As Variant

Next, for this variable, we have assigned the value 43586.

Code:

MyVal = 43586

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

Code:

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

Let us 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.”

You must wonder if we have supplied the serial number, but the result shows the date. Because Excel stores the date as serial numbers, 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

• It will also apply your system's default date to your Excel.
• We can use the Number Format property to change the date format in VBA.
• Using the FORMAT function, we can change the date format.
• Excel stores date as serial numbers. If you apply the date format, it will show accordingly.