How to Format Date in VBA

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:

Graphical user interface, text, table

Description automatically generated with medium confidence

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:

Graphical user interface, application

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

Once there, we can set the format of our cells with the following code:

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:

Graphical user interface, text, application, email

Description automatically generated

Once we execute the code by clicking F5 on our keyboard, this is what we will end up within our worksheet:

Graphical user interface, text, application

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

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.

Format function has two mandatory requirements:

  1. Expression– the word or string that we want to format in a certain way.
  2. Format– the exact format that we want to use.

When we start to type in the formula, we will see all the possible parameters:

Graphical user interface, text, application, email

Description automatically generated

This is the code that we used:

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:

Graphical user interface, application

Description automatically generated

Which is exactly the result we were hoping for.

Posted in vba