Excel VBA Format Date
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 codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more is not a tough task.
How to Change Date Format in VBA?
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.
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.
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.
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.
Change Date Format by Using FORMAT Function
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.
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).
Dim MyVal As Variant
Next, for this variable, we have assigned the value 43586.
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.”
MsgBox Format(MyVal, “DD-MM-YYY”)
Let us run the code and see the result in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.
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.”
Sub Date_Format_Example3()
Dim MyVal As Variant
MyVal = 43586
MsgBox Format(MyVal, “DD-MM-YYY”)
End Sub
Things to Remember
- 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.
Recommended Articles
This article 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 functions, examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –
- Copy Formatting in ExcelSubtract Date In ExcelCDATE Function in VBARange Variable in VBAVBA RGB
