Get Today Date in VBA

In VBA, you may get today’s date by: 1- Using Date() function 2- Now() function

How to get today’s date in VBA?

In VBA, you may get today’s date by:

  • Using Date() function
  • Now() function

First Way - An example to get today's date by Date() function

We declared a date-type variable and assigned it the Date().

Then we displayed that in the message box.

VBA program:

Sub today_ex()

Dim dt As Date
dt = Date

MsgBox (dt)

End Sub

Output:

VBA-today-date

Display today’s date in day, Month year format

By using Date() function in the Format() function, you may get the date in the desired format.

In this example, we will get today’s date in day, Month year name format. For example,

21 March, 2021

Code:

Sub today_ex()

Dim dt As String

dt = Format(Date, "dd mmmm, yyyy")

MsgBox (dt)

End Sub

Output:

VBA-today-dd-mmmm-yyyy

Notice that, we declared a variable of String type. If you use a date type variable, it will not format the date.

Short Month name with Day and Year

For example, 21 Mar, 2021.

For that, use three times mmm:

Code:

Sub today_ex()

Dim dt As String

'Date with short month name

dt = Format(Date, "dd mmm, yyyy")

MsgBox (dt)

End Sub

Output:

VBA-today-short-month

Second way – Using Now() function to get today’s date

In this way, we will get today’s date by using the Now() function.

Now() function returns the current date and time.

Sub today_ex()

Dim dt As String

'Now to get Today date

dt = Now()

MsgBox (dt)

End Sub

Output:

VBA-today-Now

You see, not only it displays today’s date but time also.

Get only the date part by Now() function

Sub today_ex()

Dim dt As String

'Now() to get Today date only

dt = Format(Now(), "dd/mm/yyyy")

MsgBox (dt)

End Sub

Output:

VBA-today-date-only

You may use the same formatting options as shown with the Date() function above.