VBA Date Function with Formatting Examples

The VBA Date function returns the system’s current date.
This is a variant type.
It can be used with the format function to get the date in the desired format.
We will show this in examples.

We will display the Date in the message box to get the current date:

Sub Date_ex()


MsgBox (Date)

End Sub

Output:

You can see, it displays the current date of the system – in the set format.

Using Date() alone might not be as meaningful as you might want to display in your Excel sheet.
Now let us have a look at formatting date by using the Date function with Format().
So first, we will show you using a Date type variable and assigning it the Date() function and using it in the Format() function.

Code:

Sub Date_ex()

Dim Dt As Date

Dt = Date

MsgBox (Format(Dt, "dd mmmm yy"))


End Sub

Result:

To get the date with the full month name, use ‘m’ four times.

Just adding two more ‘yy’ to the above example and it will get us a year in four digits.

Sub Date_ex()

Dim Dt As Date

Dt = Date

'Four digit year

MsgBox (Format(Dt, "dd mmmm yyyy"))


End Sub

Result:

Sub Date_ex()

Dim Dt As Date

Dt = Date

'Four digit year

MsgBox (Format(Dt, "yyyymmdd"))




End Sub

Result

Sub Date_ex()

Dim Dt As Date

Dt = Date

'Short Month Name

MsgBox (Format(Dt, "dd mmm, yyyy"))


End Sub

Result:

Use ‘ddd’ for the short day name as follows:

Sub Date_ex()

Dim Dt As Date

Dt = Date

'Short Day Name, Short Month

MsgBox (Format(Dt, "ddd, dd mmm, yyyy"))




End Sub

By using the Date() function with the Format() function, we will display the date in full day name, short month, and four-digit year:

Sub Date_ex()

Dim Dt As Date

Dt = Date

'Full Day Name, Short Month

MsgBox (Format(Dt, "ddd, dd mmm, yyyy"))




End Sub