VBA DateAdd Function

What is Excel VBA DateAdd Function?

The VBA DATEADD function is a time intelligence function listed under Date and Time functions category. The function inputs the date and adds or subtracts days, months, and years from the given date.

Hence, the VBA DATEADD function will return the date as the output. We can add or subtract days, months, and years to one date and get the new date in return. For example, assume we have a date “15-Jan-2023,” and we want to add ten days. We can use the following code.

Sub DateAdd_Intro()
Dim Mydate As Date
Mydate = “15-Jan-2023”
Mydate = DateAdd(“d”, 10, Mydate)
MsgBox Mydate
End Sub

Once we run the code, we will get the new date in return in a message box, i.e., “25-Jan-2023.”

Syntax of VBA DateAdd Function

Following is the syntax of the VBA DateAdd function.

DateAdd( Interval As String, Number as Double, Date)

• Interval: In this argument, we will decide what needs to be done, i.e., whether we need to add or subtract day or month, or year.

Based on the requirement, we can provide the following inputs to the interval argument of the VBA DateAdd function.

Interval	Difference Unit
d	Add or Subtract Day
m	Add or Subtract Month
q	Add or Subtract Quarter
yyyy	Add or Subtract Years
w	Add or Subtract Weekday
y	Add or Subtract Day of the Year
www	Add or Subtract Week of Year
h	Add or Subtract Hour
n	Add or Subtract Minute
s	Add or Subtract Second

• Number: Here, we can give the number based on the interval entered in the first argument. We can provide positive numbers for adding and negative numbers for subtracting.
• Date: The actual date to which we try to add or subtract the day, the month, or the year.

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.

How to Use DateAdd Function in VBA?

Let us show you a simple example of using the VBA DATEADD function with a step-by-step approach. Assume we have a date “20-05-2023.” We must add 2 days to the date.

Step 1: Start the sub procedure by naming the macro.

Step 2: Define a variable to store the original date value with the Date data type, i.e., “20-05-2023.”

Step 3: Assign the date “20-05-2023″ to the defined variable.

Step 4: Define another variable of data type Date to store the new date that will be returned by the VBA DateAdd function.

Step 5: We will assign the value through the VBA DateAdd function for this new variable. Hence, enter the variable name and open the VBA DateAdd function.

Step 6: The first argument of the function is “Interval.” Here we are trying to add two days to the date. Hence enter the interval argument as “d.”

Step 7: Since we must add two days to the date, enter two as the number argument of the VBA DateAdd function.

Step 8: Next, we must enter the date for which we are trying to add or subtract another day. In this case, our date is 20-05-2023, assigned to the variable “Original_Date.” Hence, give this variable name.

Step 9: Close the bracket.

Step 10: Finally, show the new Date value in a message box.

Sub DateAddd_Basic()
Dim Original_Date As Date
Original_Date = “20-05-2023”
Dim New_Date As Date
New_Date = DateAdd(“d”, 2, Original_Date)
MsgBox New_Date
End Sub

Let’s execute the code, and we will get the in-date in return.

Our original date was “20-05-2023,” and we have added two days to it; hence we get the new date 22-05-2023, i.e., two days after the original date.

Examples of VBA DateAdd Function

Let us show you a variety of VBA DateAdd function practical examples.

Example #1: Add Date

We can add a specific number of days to the date to get a new date in return. Consider the date “14-03-2023” and add ten days to it.

The following code will add ten days and return the new date.

Code Explanation:

Part #1: Here, we have defined a variable “MyDate” and assigned the date “15-03-2023” to it. Henceforth, when we refer to this variable, it holds the data “15-03-2023”.

Part #2: We have defined another variable named “New_Date.” We are assigning it a value through the VBA DateAdd function. We have used the interval “d” to add ten days to the date.

Part #3: We last show the new date value returned by the VBA DateAdd function in the message box. Let’s run this code, and we will see the following new date in a message box.

The initial date was 15th Mar 2023; since we added ten days, we get the new date in return, i.e., 25th Mar 2023.

Example #2: Add Months

We will add five months for the same date as the previous example. To add months, we need to change the interval argument of the VBA DateAdd function.    

Let’s use the same code from the previous example and change only the interval argument from “d” to “m” and the number from 10 to 5.

Sub DateAdd_Ex2()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “15-03-2023”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“m”, 5, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

Let’s execute the code and we will get the new date in return in a message box.

The original date was “15-03-2023.” Since we have added 5 months to the date only the month has been changed from 3 to 8 i.e., 3+5 = 8.

Example #3: Add Years

Similarly, for the same date, we can add many years. For example, currently, the year part of the date is 2023. If we add two years, it will be 2025, while all the other parts of the date remain the same.

Sub DateAdd_Ex3()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “15-03-2023”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“yyyy”, 2, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

We have changed the interval argument value to “yyyy” and the number to 2. It will add two years to the year part of the given date, making it 15th March 2025.

Example #4: Add Quarter

The quarter will add three months to the current date. For example, if the current date is 15^th March 2023 and if we add one quarter, we get the new date as 15^th June 2023. For example, look at the following VBA code.

Sub DateAdd_Ex4()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “15-03-2023”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“q”, 1, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

We have changed the interval to “q” and the number to 1. It adds three months to the given date; only the month part will change, and the rest will remain the same.

Now let’s look at the exciting scenario. For example, assume the date is 15th November 2023, and if we add one quarter to it, we will get the following year’s date, i.e., the year 2024 date. Look at the following code.

Sub DateAdd_Ex4()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “15-11-2023”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“q”, 1, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

When we run this code, we get the following new date.

The current date, 15th November 2023, gets three months added to it, and the new date will move to the next year.

Example #5: Add Weekdays

It is like adding many days to the date. As it says weekdays, you may expect the result to exclude weekends, i.e., Saturday and Sunday. However, when we pass “w” to the interval argument of the VBA DateAdd function, it just adds the specified number of days to the given date.

Sub DateAdd_Ex5()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “21-07-2023”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“w”, 1, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

It will return the following date.

Example #6: Add Week

Basically, “ww” will add seven days to the given date. In this case, the current date is 15th July 2023, and because we are adding one week, it will add seven days to the given date and will return on 28th July 2023 as the new date.

Sub DateAdd_Ex6()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “21-07-2023”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“ww”, 1, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

Example #7: Add Hour

VBA DateAdd function not only works with dates, but it also works with time. We can add hours, minutes, and seconds to the time part of the date/time value. For example, if we have a date/time value of “21-07-2023 14:15:00 PM” and want to add one hour, we use the following code.

Sub DateAdd_Ex7()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “21-07-2023 14:15:00 PM”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“h”, 1, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

Once we run this code, we will get the following new time.

Example #8: Subtract Date

VBA DateAdd function not only supports addition but also the subtraction of dates as well. All we must do is give it negative numbers.

For example, if we have the 21st of July 2023, if we give the number as -1 with a “d” interval, we will get the new date as the 20th of July 2023. For example, look at the following code.

Sub DateAdd_Ex8()
‘Define a variable to assign a date value
Dim Mydate As Date
‘Assing a date to the defined variable
Mydate = “21-07-2023”
‘Define variable to assign new date
Dim New_Date As Date
‘Assign the new date returned by the DateAdd function
New_Date = DateAdd(“d”, -1, Mydate)
‘Show the new date in a message box
MsgBox New_Date
End Sub

Once we run this code, we will get the following new date.

Important Things to Note

• VBA DateAdd function accepts only date and time values; if the given value is not a date and time value, we get a Type Mismatch error.
• We will get a Type Mismatch error in VBA if the given interval value is not recognizable.
• The output format of the VBA DateAdd function depends on the system date format. For example, in some systems, it may show 12-30-2023; in others, it may show 30-12-2023.
• The interval “w” does not exclude weekend days; rather, it works like the “d” interval.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VBA DateAdd Function formulas and examples. We can download the template here to use it instantly.

Recommended Articles

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

• VBA DateDiff Function
• VBA Date Function
• VBA DatePart