VBA Date Function

What is Excel VBA Date Function?

The Excel VBA Date function format is a built-in function that returns the current system date as a Date data type in VBA . The Date function does not require any arguments and can be used to retrieve the current date in Excel VBA. It returns the value which the VBA Date Function provides.

The Date function has different use cases, ranging from finding the difference between dates, returning the current time, printing the current date, and printing only the date’s month, day, or year with the Date’s help. The VBA Date function day month year format is generally accepted as arguments from the user. For example, consider the following code:

It will print today’s date in the Immediate tab.

How to use Excel VBA DATE function?

To access the VBA DATE function, write the necessary code to implement it and keep it ready.

Step 1: Go to the “Developer” tab in excel in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Step 2: Consider an example where we need to find the difference in the number of days with a given date and today’s date using the VBA DATE Function reference library.

Step 3: Initialize a subroutine to print the difference between the dates.

Step 4: Define three variables, “currentDate” to hold the value for the DATE function, “futureDate” as the date given, and the “daysDifference” to hold the difference between the two dates.

Step 5: Initialize “currentDate” with the value returned by the DATE function and initialize “futureDate” by giving its value.

Step 6: Print the difference of days by subtracting “futureDate” and “currentDate” and printing it.

Here, vbNewLine enables all values to be printed in the same Debug.Print function and will print the output in a new line.

Code:

Sub DateExample()
Dim currentDate As Date
Dim futureDate As Date
Dim daysDifference As Integer
currentDate = Date
futureDate = currentDate + 7
daysDifference = futureDate – currentDate
Debug.Print “Current Date: ” & currentDate & vbNewLine & _
“Future Date: ” & futureDate & vbNewLine & _
“Difference between the dates: ” & daysDifference
End Sub

Step 6: Run the code by pressing the F5 or Run button. It will print as mentioned above.

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.

Examples

Here are a few examples to help understand the VBA DATE division and its use cases.

Example #1

Consider an example where you build a VBA process to check whether a given input date from the user is in the past, present, or future. The user accepts the date value using the “InputBox()” VBA function.

Step 1: Define a subroutine to check whether the given date is past, present, or future.

Step 2: Declare two variables, ‘inputDate’ and ‘currentDate’, as the datatype “Date.” The variable “inputDate” is used to accept the date variable from the user, and the variable “currentDate” is used to store the Date value given by today VBA Date function.

Step 3: Accept custom input from the user for “inputDate.” Initialize “currentDate” by storing the value of the Date Function.

Step 4: Initialize an If-Else statement to check whether the given date is lesser than the current date; then, it will be defined as “past.” Else If not, returns “future.” Else, it returns “today.”

Code:

Sub CheckDate()
Dim inputDate As Date
Dim currentDate As Date
inputDate = InputBox(“Enter a date (dd/mm/yyyy):”)
currentDate = Date
If inputDate < currentDate Then Debug.Print “The entered date is in the past.” ElseIf inputDate > currentDate Then
Debug.Print “The entered date is in the future.”
Else
Debug.Print “The entered date is today.”
End If
End Sub

Step 5: To run the code, click the green arrow button in the VBA toolbar.

Step 6: Run the VBA code. The output is printed in the Immediate tab.

Example #2

Suppose you want to calculate your age using Excel VBA. It can be found by subtracting your birth year and current year to return your age.

Step 1: Form a new subroutine to calculate your age.

Step 2: Declare two variables, ‘dob’ and ‘age,’ with the datatype Date, where ‘dob’ is used to input your date of birth and age is the integer value to store your age.

Step 3: Initialize the value for dateRange as the range of the given table (it can be changed based on the preferred range) and counter as 0.

Step 4: The variable “age” is the difference between the variable “dob” and today VBA DATE function.

Print “age” using the MsgBox function. It will print the age with a Message Box popup.

Code:

Sub CalculateAge()
Dim dob As Date
Dim age As Integer
dob = InputBox(“Enter your date of birth (dd/mm/yyyy):”)
age = DateDiff(“yyyy”, dob, Date)
MsgBox “Your age is: ” & age
End Sub

Step 5: To run the code, click the green arrow button in the VBA toolbar.

Step 6: The resulting output is as shown.

Example #3

Consider an example where you print the year, month, and day of a given date separately as VBA Date Function day month year. It can be done using the VBA DatePart() Function.

Step 1: Initialize a subroutine to split and print the components of a date separately.

Step 2: Initialize a Date variable “myDate” and assign it the value given by the VBA Date function.

Step 3: Assign the year of the date to a variable.

Step 4: Assign the month part of the given date to a variable.

Step 5: Assign the day part of the given date to a variable.

Step 6: Print all three variables with the MsgBox function.

It will print all the values in the same MsgBox but in different lines with the help of vbNewLine.

Code:

Sub ExtractDateParts()
Dim myDate As Date
myDate = Date
Dim yearPart As Integer
yearPart = DatePart(“yyyy”, myDate)
Dim monthPart As Integer
monthPart = DatePart(“m”, myDate)
Dim dayPart As Integer
dayPart = DatePart(“d”, myDate)
MsgBox “Year: ” & yearPart & vbNewLine & _
“Month: ” & monthPart & vbNewLine & _
“Day: ” & dayPart
End Sub

Step 7: To run the code, click the green arrow button in the VBA toolbar.

Step 8: After running the code, the output is shown below.

Important Things To Note

• Use the Date function to retrieve the current system date.
• The DateAdd function can add a specified time interval to a date.
• The DateDiff function is used to calculate the difference between two dates.
• We use the DatePart function to extract a specific date part.
• Use the DateSerial function to create a date value from individual year, month, and day components.
• The Date function cannot retrieve the current system time.
• Don’t use the Date function to perform time-based calculations with invalid date formats or inputs.
• Be mindful of date format consistency, handling invalid dates, and considering regional settings when working with the Date function.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to What is VBA DATE Function. We learn how to use date function to return the current date with examples. You can learn more from the following articles –

• VBA DateSerial
• VBA CDATE Function
• DATEVALUE In Excel