VBA DateAdd

DateAdd in VBA

Using VBA DateAdd function we can add and subtract days, months, and years from the given date. Date in excel is part and parcel of our daily work, we cannot work in excel without date, time and other important stuff. Adding one date to another date, subtracting one date from another is the common process. In the regular worksheet, we simply do arithmetic operations and get the result.

In VBA we have the function called DateAdd which will do the job. In this article, we will explain to you about DateAdd function in VBA.

Formula of DateAdd Function in VBA

The syntax of DateAdd function includes 3 arguments and all are mandatory.

Interval: Interval is nothing but what is the kind of value you want to add or subtract. For example, whether you want to add or subtract a month, whether you want to add or subtract days, whether you want to add or subtract a year, whether you want to add or subtract quarter, etc…

Below are a list of codes and its descriptions.

• Number: The number of months, days or week (as provided in the interval) we want to add or subtract to the date.
• Date: The actual date value we are doing the arithmetic operation.

For example, if you have the date “14-Mar-2019” and you want to add 2 days to the date below is the code:

DateAdd (“d”, 2, “14-03-2019”)

The result of the above code is: 16-03-2019

Examples of Excel VBA DateAdd Function

After all the theory question is how to use. Ok, let us jump into practicality now. Follow the below steps to write the code for VBA DateAdd.

Example #1 – Add Date

To start the proceedings lets apply simple DateAdd function. Assume you are working with the date “14-03-2019”.

Step 1: Start the sub procedure by creating a macro name.

Step 2: Define the variable as Date.

Code:

Sub DateAdd_Example1()

Dim Month As Date

End Sub

Step 3: For this variable assign value.

Code:

Sub DateAdd_Example1()
  Dim NewDate As Date

  NewDate =

End Sub

Step 4: Start the DateAdd formula.

Code:

Sub DateAdd_Example1()
  Dim NewDate As Date

  NewDate = DateAdd(

End Sub

Step 5: What is the operation we want to do. We want a day to the date. So the interval is “d”.

Code:

Sub DateAdd_Example1()
    Dim NewDate As Date

   NewDate = DateAdd("d",

End Sub

Step 6: How many days we need to add, I have to add 5 days.

Code:

Sub DateAdd_Example1()
   Dim NewDate As Date

   NewDate = DateAdd("d", 5,

End Sub

Step 7: Our date is “14-03-2019”

Code:

Sub DateAdd_Example1()
   Dim NewDate As Date

   NewDate = DateAdd("d", 5, "14-03-2019")

End Sub

Step 8: Show the result of the variable in the VBA message box.

Code:

Sub DateAdd_Example1()
  Dim NewDate As Date

  NewDate = DateAdd("d", 5, "14-03-2019")
  MsgBox NewDate

End Sub

If I run this code I should get the date as 19-03-2019.

In my system date format is “mm-dd-yyyy”, so the result is showing as per the system date settings.

However, we can modify this by applying VBA FORMAT function.

Code:

Sub DateAdd_Example1()
  Dim NewDate As Date

  NewDate = DateAdd("d", 5, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy")

End Sub

Now the result should in like this “19-Mar-2019”.

Example #2 – Add Months

To add months below is the code

Code:

Sub DateAdd_Example2()
  'To add months
  Dim NewDate As Date

  NewDate = DateAdd("m", 5, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy")

End Sub

The result will be:

Example #3 – Add Years

To add years below is the code

Code:

Sub DateAdd_Example2()
  'To add year
  Dim NewDate As Date

  NewDate = DateAdd("yyyy", 5, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy")

End Sub

The Result will be:

Example #4 – Add Quarter

To add quarter below is the code

Code:

Sub DateAdd_Example2()
  'To add quarter
  Dim NewDate As Date

  NewDate = DateAdd("Q", 5, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy")

End Sub

The Result will be:

Example #5 – Add Weekday

To add weekdays below is the code

Code:

Sub DateAdd_Example2()
  'To add weekdays
  Dim NewDate As Date

  NewDate = DateAdd("W", 5, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy")

End Sub

The Result will be:

Example #6 – Add Week

To add week below is the code

Code:

Sub DateAdd_Example2()
  'To add Week
  Dim NewDate As Date

  NewDate = DateAdd("WW", 5, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy")

End Sub

The Result will be:

Example #7 – Add Hour

To add hour below is the code

Code:

Sub DateAdd_Example2()
  'To add hour
  Dim NewDate As Date

  NewDate = DateAdd("h", 5, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy hh:mm:ss")

End Sub

The Result will be

This is how we can work with excel DateAdd function in VBA.

Example #8 – Subtract in VBA DateAdd Function

In order to add we have supplied positive numbers, to subtract we need to supply negative numbers that are all.

In order to subtract 3 months from the supplied date below is the code.

Code:

Sub DateAdd_Example3()
  'To add hour
  Dim NewDate As Date

  NewDate = DateAdd("m", -3, "14-03-2019")
  MsgBox Format(NewDate, "dd-mmm-yyyy")

End Sub

The result of this code is:

If deduct 3 months from the date 14^th March 2019 it will go back to the previous year.

Recommended Articles

This has been a guide to Excel VBA DateAdd Function. Here we learne how to use VBA DateAdd function to add & subtract days, months & years from the given date along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• VBA Weekday
• Excel VBA Set
• VBA Select Case Statement
• VBA Cell Reference Property
• Using VBA FIND Function
• Using VBA Loop