How to use Date function in VBA?

The Date function in VBA (Visual Basic for Applications) is used to return the current system date. It’s a straightforward function, and here’s how you can use it in various contexts:

Basic Usage

To get the current date, simply use the Date function:

Dim currentDate As Date
currentDate = Date

This assigns the current system date to the variable currentDate.

Displaying the Date

You can display the date using the MsgBox function or output it to a sheet in Excel:

MsgBox "Today's date is " & Date

Or, if you are using it in Excel:

ActiveSheet.Range("A1").Value = Date

Formatting the Date

To format the date, you can use the Format function:

Dim formattedDate As String
formattedDate = Format(Date, "dd/mm/yyyy")

This will format the date in the day/month/year format.

Using Date in Calculations

The Date function can be used in date calculations. For example, to find a date that is 10 days from now:

Dim futureDate As Date
futureDate = Date + 10

Similarly, you can subtract days:

Dim pastDate As Date
pastDate = Date - 10

Comparing Dates

You can also use the Date function to compare dates:

If Date > someOtherDate Then
    MsgBox "Today's date is after someOtherDate."
End If

Using Date with Time

If you need the current date and time, use the Now function instead:

Dim currentDateTime As Date
currentDateTime = Now

Getting Parts of a Date

To get specific parts of the date, like the year, month, or day, you can use Year, Month, and Day functions:

Dim year As Integer
Dim month As Integer
Dim day As Integer

year = Year(Date)
month = Month(Date)
day = Day(Date)


  • The Date function always returns the current system date as set on the user’s computer.
  • Remember that date formats might appear differently based on the system’s regional settings.

The Date function in VBA is incredibly useful for any task that requires date manipulation, comparison, or display.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *