VBA TODAY

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.”

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

Where,

• 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.

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.

Here,

• 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.

Here,

• “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.

Code:

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.

Examples

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.

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.

Code:

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.

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.

Code:

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.

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)

Recommended Articles

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 –

• VBA Random Number
• VBA Union
• VBA Substring