You are probably familiar right now with the fact that everything that can be done in Excel can also be done through VBA, in most cases even faster.
Formatting the date is not the exception to this. In the example below, we will show how to do it with the two most used options.
Format Date in VBA with NumberFormat
We use the NumberFormat property to format the numbers that can already be found in our Workbook. We will insert three random numbers in column A:
When opening the workbook, you should be aware that the default format of the numbers is General. We can change the format of these numbers either through the Home tab (number format) or through the VBA:
To write the code in VBA, we first need to open it. We can do it by clicking ALT + F11 on our keyboard to open the VBA, then right-click on the left window and select Insert >> Module:
Once there, we can set the format of our cells with the following code:
|
1 2 3 4 5 |
Sub NumberFormats() Range("A2").NumberFormat = "mm/dd/yyyy" 'short date Range("A3").NumberFormat = "dddd, mmmm dd, yyyy" 'long date Range("A4").NumberFormat = "mmmm,yy" 'custom date End Sub |
And in the module, it looks like this:
Once we execute the code by clicking F5 on our keyboard, this is what we will end up with our worksheet:
As seen, the NumberFormat property can be used to format our range in any type of date we want. We can also use the Home tab to find various possible formats of dates, by going to Home >> Number Formatting >> Custom and then scrolling down to find various options for dates:
We can type any of these formats in the VBA to get the desired results.
Format Date in VBA with Format Function
The NumberFormat can be used for formatting the dates of the cells that are located in the Workbook. In the VBA itself, we can use the Format Function to format dates.
The format function has two mandatory requirements:
- Expression– the word or string that we want to format in a certain way.
- Format– the exact format that we want to use.
When we start to type in the formula, we will see all the possible parameters:
This is the code that we used:
|
1 2 3 |
Sub FormatFunction() MsgBox Format(Date, "mm/dd/yyyy") End Sub |
What this function does is it takes today’s date and formats it in the following way: “mm/dd/yyyy”.
When we execute the code by pressing F5, this is what will be shown on our screen:
Which is exactly the result we were hoping for.