VBA DateAdd

Excel VBA DateAdd

VBA DateAdd is a function which performs addition or subtraction of time/date intervals. This will return date by adding or subtracting a specified time interval. It is quite difficult to deal with date and time when you do some calculations on it. But in our daily work, it is an essential type of data that we may use. Comparison, addition, subtraction between different dates are some familiar operations that we do.

Formula For DateAdd function in Excel VBA

The formula for VBA DateAdd function is very simple in format.

Let’s see what are the parameters used in the Excel VBA DateAdd function.

• Interval: This can be a time/date interval that you want to add or subtract. This represents what kind of value you wish to add or subtract. This can be a component of date or time like days, month, etc. The scope of intervals is given below.

• Number: Is the number of intervals you want to add. Use a positive number to add the interval with the given date and negative value to subtract the interval from the date.
• Date: The date to which you want to add/subtract the interval. Operations will be performed on this date and return date as output.

Examples of Excel VBA DateAdd Function

Below are the different examples of DateAdd Function in Excel VBA:

Example #1 – Add Date

Let’s see how to add a particular number with the given date using VBA DateAdd Function.

• We need to find the date after adding ’10’ days with the date ‘25/10/2015’
• Start sub procedure with a name. Here we created a sub procedure named ‘adddate’.

Code:

Sub adddate()

End Sub

• Create a variable as date datatype, variable name is currentdate.

Code:

Sub adddate()

Dim currentdate As Date

End Sub

• We are going to store the result in this variable currentdate.
• We want to add ‘10’ days with ‘25/10/2015’. So the number of days want to add is 10. And the interval is ‘d’ and the number is 10.
• So let’s apply the VBA DateAdd function as below.

Code:

Sub adddate()

Dim currentdate As Date
currentdate = DateAdd("d", 10, "25/10/2015")

End Sub

• After applying the formula to the variable let’s use a message box to print the result.

Code:

Sub adddate()

Dim currentdate As Date
currentdate = DateAdd("d", 10, "25/10/2015")
MsgBox Format(currentdate, "dd-mm-yyyy")

End Sub

• Run the code by pressing F5. The result will be shown as

You can see the result as shown above.

Example #2 – Add Months

• To add months with the given date the interval needs to change as “m”.
• Add ‘2’ with the date “15/2/2017”. The code can be written as below.

Code:

Sub addmonth()

Dim currentdate As Date
currentdate = DateAdd("m", 2, "15/2/2017")
MsgBox Format(currentdate, "dd-mm-yyyy")

End Sub

• The output date will be changed as below.

Example #3 – Add Year

To add years with the given date the below code can be used.

• The interval should be” yyyy”
• Add 4 years with’20/2/2018’

Code:

Sub addyear()

Dim currentdate As Date
currentdate = DateAdd("yyyy", 4, "20/2/2018")
MsgBox Format(currentdate, "dd-mm-yyyy")

End Sub

• The result will be as below. The variable currentdate will return ‘20/2/2022’

Example #4 – Add Quarter

• While adding quarter, three months will be added to the date since the quarter if 12 months is 3.
• The interval should be mention as “Q”, the number given in the formula specifies how many quarters should be added. For example, DateAdd(“Q”,2, ”22/5/2019”) number of quarters is 2 so 6 months will be added.
• To add 2 quarters with ‘22/5/2018’ below code can be used.

Code:

Sub addquarter()

Dim currentdate As Date
currentdate = DateAdd("Q", 2, "22/5/2019")
MsgBox Format(currentdate, "dd-mm-yyyy")

End Sub

• The result will be as below.

Example #5 – Add Seconds

• You can add time along with date displayed. To get this mention the interval as “s” which indicates seconds.
• To display five seconds with date ‘28/3/2019’ can use the below formula.

Code:

Sub addseconds()

Dim currentdate As Date
currentdate = DateAdd("s", 5, "28/3/2019")
MsgBox Format(currentdate, "dd-mm-yyyy hh:mm:ss")

End Sub

• While showing the output with date seconds will be displayed.

Example #6 – Add Weeks

• To add a number of weeks with the given date, use the interval as “WW”
• Code to find the date after the given number of weeks from’27/03/2016’

Code:

Sub addweek()

Dim currentdate As Date
currentdate = DateAdd("WW", 2, "27/3/2019")
MsgBox Format(currentdate, "dd-mm-yyyy")

End Sub

• The output will be the date after 2 weeks.

Example #7 – Add Hours

• To get a particular time with a date this is used.
• In interval mention the “h” and also change the format of the output.
• The code to get the hours printed with a date is.

Code:

Sub addhour()

Dim currentdate As Date
currentdate = DateAdd("h", 12, "27/3/2019")
MsgBox Format(currentdate, "dd-mm-yyyy hh:mm:ss")

End Sub

• The result will be shown with time in hh:mm:ss.

Example #8 – How to Subtract Weeks using VBA DateAdd Function?

Similar to addition, subtraction can also perform using VBA DateAdd function. The numbers specified as positive integers along with the formula. To perform subtraction, use these numbers as negative integers. For example, change the formula as below.

DateAdd (interval, - number, date)

By using the above method will try to find the day subtracting three weeks from ‘28/3/2019’

• Create a subprocedure as subdate.

Code:

Sub subdate()

End Sub

• Define a variable to store the result. Currentdate is a variable as date type to assign the final result.

Code:

Sub subdate()

Dim currentdate As Date

End Sub

• To subtract three weeks from ‘28/3/2019’ will apply the formula. DateAdd(“ww”, -3, “28/3/2019”)

Code:

Sub subdate()

Dim currentdate As Date
currentdate = DateAdd("ww", -3, "28/3/2019")

End Sub

‘-3’ indicates the subtraction “ww” is the interval since we want to operate on weeks.

• The formula is applied and the result is stored in currentdate.

Code:

Sub subdate()

Dim currentdate As Date
currentdate = DateAdd("ww", -3, "28/3/2019")
MsgBox Format(currentdate, "dd-mm-yyyy")

End Sub

• The result after subtracting three weeks from the given date is displayed below.

Things to Remember

• The interval and date mentioned in the formula will be given within a double quotation.
• If you use weekdays interval” w” it will work similarly to the interval day “d” since the weekday calculates 1=Sunday, 2=Monday, etc. in So it will count the holidays even you use weekdays.
• The out will be displayed according to the date format settings on your system. Use format along with a message box to print the result in the format you want.
• Within the VBA DateAdd function use the number as negative to perform subtraction among dates.

Recommended Articles

This is a guide to Excel VBA DateAdd Function. Here we discuss the examples of VBA DateAdd function to add & subtract days, months & years from the given date along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. How to Use Date Function in VBA?
2. YEAR in Excel | Formula | Examples
3. What is VBA Time in Excel?
4. Date Formula in Excel with Examples