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:
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.
The VBA today date format is in the format “dd-mm-yyyy.”
Table of Contents
- 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.
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.
After you click the Developer icon, select “Visual Basic.” It opens the VBA window where you can start coding.
In the VBA Module, click the “Insert” button in the title bar and select the “Module” option.
Now, you are ready to start coding in Excel VBA.
Step 2: Create a subroutine to display VBA Today Date Format.
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.
Step 4: Initialize three string variables to store different VBA Today date formats.
Step 5: Return the current date in the “MM/DD/YYYY” format using the VBA Format function.
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.
- 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.
Step 8: Using the MsgBox function, print all three formats.
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.
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
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.
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.
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.
Step 3: Check whether the current year’s birthdate has passed. If not, subtract one from the difference in years gotten.
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.
Step 5: Declare variables to store the birthdate, VBA Today’s date, and the age returned from the calculations as an integer.
Step 6: Get the birthdate from the user.
Step 7: Get the current date and store it in a variable.
Step 8: Call the function defined earlier to calculate the age and insert the birth date and the current date as its arguments.
Step 9: With the MsgBox function, print the birth date, current date, and age.
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:
We can infer that the age of the person is 10 years.
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
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
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.
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.
Step 3: Initialize a sub-procedure that calls the above function to perform VBA Today plus 7 days.
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.
Step 5: Store the current date in the first VBA Date variable.
Step 6: Initialize the values to be added to the current day: the number of days and hours.
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.
Step 8: Print the input and current dates returned after adding the day and time.
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.
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”)
Function AddDaysAndHours(inputDate As Date, days As Integer, hours As Integer) As Date
AddDaysAndHours = inputDate + days + hours / 24
Step 9: Click the green arrow button when you want to run the code. It will pop up a Message Box once run.
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)
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
Replace SomeDate with the date you want to compare to today’s date.
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”.
● 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.”
● 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 –