What is Excel VBA Today Function?

In VBA (Visual Basic for Applications), the “Today” function is not a built-in function like in other programming languages or applications. Instead, you can use VBA to work with dates and times and create your function to get the current date.

Consider the following example:

VBA Today Function

Here, we create a function to return today’s date, then implement it in a subroutine where the custom function is called in the subroutine to get it printed. We use the VBA Date function while creating the function for returning VBA today’s date. It prints the current date in the Immediate tab, as seen below.

VBA Today Function 1

The VBA today date format is in the format “dd-mm-yyyy.”

Key Takeaways
  • The VBA Date function is used to obtain the current system date and time in Visual Basic for Applications. It returns a Date data type representing the current date and time.
  • This function does not require any arguments and can be used to capture the current date and time within the VBA code.
  • Developers can then manipulate and use this date-time information for various purposes, such as date calculations, time-sensitive tasks, or for timestamping data.
  • The Date function can be used in various VBA-enabled applications, such as Microsoft Excel, Access etc.
  • If you need to capture a changing date and time continuously, you may have to call the function multiple times as needed in your code.

What is the Formula to get Today’s Date in VBA?

To get VBA Today’s date, we use the “Date” function built into Excel VBA to return today’s date.

The formula is:

CurrDate = Date


  • CurrDate: Variable to store today’s date.
  • Date: VBA Function used to return today’s date.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

What Date Function Does in VBA?

Below, we show you how the Date function is implemented, with an example.

Step 1: Once you open the Excel Workbook, choose the “Developer” icon in the title bar and click on it.

VBA Today Step 1

After you click the Developer icon, select “Visual Basic.” It opens the VBA window where you can start coding.

VBA Today Step 1-1

In the VBA Module, click the “Insert” button in the title bar and select the “Module” option.

VBA Today Step 1-2

Now, you are ready to start coding in Excel VBA.

Step 2: Create a subroutine to display VBA Today Date Format.

VBA Today Step 2

Step 3: Declare a variable to store the current day’s date. Use the VBA Date function to store the value returned by the Date function.

VBA Today Step 3

Step 4: Initialize three string variables to store different VBA Today date formats.

VBA Today Step 4

Step 5: Return the current date in the “MM/DD/YYYY” format using the VBA Format function.

VBA Today Step 5

It will store the VBA Today’s date in the variable’s MM/DD/YYYY format.

Step 6: Similarly, format the current date value, which will print the month name, day, and year.

VBA Today Step 6


  • MMMM- Returns the name of the current day’s month.
  • DD- Returns the day number of the month of the current day.
  • YYYY- Returns the year of the current date.

Step 7: Return the current date in the “DD-MM-YYYY” format for the third format.

VBA Today Step 7

Step 8: Using the MsgBox function, print all three formats.

VBA Today Step 8

We use the vbCrLf constant in VBA to represent a newline character sequence. It is used to insert a line break or carriage return in strings when you want to create multiline text or format the output in a way that includes line breaks.


  • “vb” stands for Visual Basic.
  • “CrLf” stands for Carriage Return and Line Feed, which are control characters used in text files and communication protocols to indicate the end of a line and move the cursor to the beginning of the next line.

Step 9: To run the program, click “F5” or the “Run” icon on the activity bar in the Excel VBA Module. It prints a Message Box with the different date formats in excel.

VBA Today Step 9


Sub ExtractAndDisplayDateFormats()
Dim currentDate As Date
currentDate = Date
Dim dateFormat1 As String
Dim dateFormat2 As String
Dim dateFormat3 As String

   dateFormat1 = Format(currentDate, “MM/DD/YYYY”)
dateFormat2 = Format(currentDate, “MMMM DD, YYYY”)
dateFormat3 = Format(currentDate, “DD-MMM-YY”)
MsgBox “Format 1: ” & dateFormat1 & vbCrLf & _
   “Format 2: ” & dateFormat2 & vbCrLf &
   “Format 3: ” & dateFormat3
End Sub

Now that we know how to use the Date function in Excel VBA let us view some interesting examples below.


Here, we can view the different ways VBA Today’s date and time can be used for manipulation.

Example #1

Given a birthdate, you are assigned a task to find the person’s current age using Excel VBA. It can be done by finding the number of years between the birth year and the current year. To get the current year, we use VBA Today’s date and find the number of years in between.

Step 1: Create a function to calculate the age of the person.

VBA Today Example 1

It returns an integer as the result.

Step 2: Subtract the number of years from the dates by using the Year() function to pull out the years of the given two dates.

VBA Today Example 1-1

Step 3: Check whether the current year’s birthdate has passed. If not, subtract one from the difference in years gotten.

VBA Today Example 1-2

For this, check using the VBA OR function if the birthday month is less than or the same as the month on the current day.

Using the VBA AND function, check the above condition for the days, using the VBA Day() and comparing the day of birth and the current day. If it’s lesser, subtract the age returned with 1.

Step 4: Create a subroutine to print a person’s age, given his birthdate.

VBA Today Example 1-3

Step 5: Declare variables to store the birthdate, VBA Today’s date, and the age returned from the calculations as an integer.

VBA Today Example 1-4

Step 6: Get the birthdate from the user.

VBA Today Example 1-5

Step 7: Get the current date and store it in a variable.

VBA Today Example 1-6

Step 8: Call the function defined earlier to calculate the age and insert the birth date and the current date as its arguments.

VBA Today Example 1-7

Step 9: With the MsgBox function, print the birth date, current date, and age.

VBA Today Example 1-8

The vbCrLf constant creates a new line and adds variables in the MsgBox instead of using different Message Boxes or printing all in the same line.

Step 10: Press “F5” to run the code. The output is displayed as shown below:

VBA Today Example 1-9
VBA Today Example 1-10

We can infer that the age of the person is 10 years.


Sub CalculateAge()
Dim birthDate As Date
Dim currentDate As Date
Dim age As Integer
birthDate = InputBox(“Enter Date”)
currentDate = Date
age = CalculateAgeFromBirthdate(birthDate, currentDate
MsgBox “Date of Birth: ” & Format(birthDate, “MMMM DD, YYYY”) & vbCrLf & _
“Current Date: ” & Format(currentDate, “MMMM DD, YYYY”) & vbCrLf & _
“Age: ” & age & ” years
End Sub

Function CalculateAgeFromBirthdate(birthDate As Date, currentDate As Date) As Integer
CalculateAgeFromBirthdate = Year(currentDate) – Year(birthDate)
If Month(currentDate) < Month(birthDate) Or (Month(currentDate) = Month(birthDate) And Day(currentDate) < Day(birthDate)) Then
CalculateAgeFromBirthdate = CalculateAgeFromBirthdate – 1
End If
End Function

Example #2

In this example, we will go through VBA Today plus seven days. We’ll see what happens if we add seven days to VBA Today’s date and three extra hours.

Step 1: Create a function to add the number of days and hours to the current date.

VBA Today Function 2

This function takes three arguments to call it. It returns the result in VBA Date format.

Step 2: We use the formula below to add the days and hours. Divide the hours by 24 to bring the number to the 24-hour format.

VBA Today Function 2-1

Step 3: Initialize a sub-procedure that calls the above function to perform VBA Today plus 7 days.

VBA Today Function 2-2

Step 4:  Initialize the input date as a Date variable, the number of hours and days to add as integers, respectively, and a new date variable to store the date value after it’s been added.

VBA Today Function 2-3

Step 5: Store the current date in the first VBA Date variable.

VBA Today Function 2-4

Step 6: Initialize the values to be added to the current day: the number of days and hours.

VBA Today Function 2-5

Step 7: Add these values by calling the function in Step 1 and defining the input date and the number of hours and days to be added.

VBA Today Function 2-6

Step 8: Print the input and current dates returned after adding the day and time.

VBA Today Function 2-7

vbCrLf is a VBA constant used to print with new lines defined in the Message Box function. Use the VBA Format function to define the current time returned when using VBA today’s date and show whether it’s AM or PM.

Similarly, do the same with the new date returned by displaying its date with the Month name, day, year, and time.


Sub ManipulateDateAndTime()
Dim inputDate As Date
Dim daysToAdd As Integer
Dim hoursToAdd As Integer
Dim newDate As Date
inputDate = Date
daysToAdd = 7
hoursToAdd = 3
newDate = AddDaysAndHours(inputDate, daysToAdd, hoursToAdd)
MsgBox “Input Date and Time: ” & Format(inputDate, “MMMM DD, YYYY hh:mm AM/PM”) & vbCrLf & _
“New Date and Time: ” & Format(newDate, “MMMM DD, YYYY hh:mm AM/PM”)
End Sub
Function AddDaysAndHours(inputDate As Date, days As Integer, hours As Integer) As Date
AddDaysAndHours = inputDate + days + hours / 24
End Function

Step 9: Click the green arrow button when you want to run the code. It will pop up a Message Box once run.

VBA Today Function 2-8

Important Things To Note

  • Be aware of the default date format used in your VBA environment. The format may vary depending on the regional settings of your computer, and this can affect how dates are displayed and interpreted.
  • The Date function in VBA returns both date and time components.
  • When performing arithmetic operations with dates, keep in mind that date values are essentially numeric values.
  • Familiarize yourself with VBA’s built-in date and time functions, such as DateSerial, DateValue, TimeSerial, and TimeValue.
  • When comparing dates, consider using comparison operators like = (equal to), < (less than), > (greater than), <= (less than or equal to), and >= (greater than or equal to) to determine the relationship between dates accurately.
  • Be cautious when working with dates and times in different time zones.

Frequently Asked Questions (FAQs)

1. How to filter today date in VBA?

To filter today’s date in VBA, use the Date function to get the current date and compare it with the date data you want to filter. For example:
If SomeDate = Date Then
    ‘ SomeDate matches today’s date
End If
Replace SomeDate with the date you want to compare to today’s date.

2. How do I set a variable to today’s date in VBA?

Use the VBA “Date” function and assign it to the variable to get VBA Today’s Date. Consider the example:
inputDate = Date
This assigns VBA Today’s date to the variable “inputDate”.

3. How do I save a VBA file with today’s date?

●  Click “File” in the VBA editor.
●  Choose “Export File.”
●  In the “Save As” dialog, specify a folder.
●  Enter a filename with today’s date, like “MyFile_2023-09-04.xlsm.”
●  Click “Save.”

4. Why is VBA today function not working?

●  Incorrect syntax or usage.
●  VBA environment settings affect the date format.
●  Date-related errors or issues in your code.
●  Reference or library problems.
●  Date system settings on your computer.

This has been a guide to VBA Today Function. Here we explain formula to get Today’s Date in VBA along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

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