WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA DateAdd Function

VBA DateAdd Function

Excel VBA DateAdd Function

DateAdd function is categorized under the date and time function in VBA and this function has an output value as a date, it takes input as a format of date and adds it to the current date to return a new date, the syntax for this function takes three arguments interval, the number and the date.

Using the 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.

VBA DateAdd Function

Syntax

VBA DateAdd Formula

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 is a list of codes and descriptions.

list of codes 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 two days to the date, use the below code:

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

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

How to Use Dateadd Function in VBA?

You can download this VBA DateAdd Excel Template here – VBA DateAdd Excel Template

Example #1 – Add Date

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

Step 1: Start the subprocedure by creating a macro name.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

VBA DateAdd Example 1

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

VBA DateAdd Example 1-1

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:

VBA DateAdd Example 2

Example #3 – Add Years

To add years using DateAdd, use the below 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:

VBA DateAdd Example 2

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:

VBA DateAdd Example 2-2

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:

Add weekday result Example 2-3

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:

Add week result Example 2-4

Example #7 – Add Hour

To add the 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

Add hour result Example 2-5

Example #8 – Subtract Date

In order to add, we have supplied positive numbers; to subtract, and 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:

subtract result Example 2-6

I deduct 3 months from the date 14th March 2019. It will go back to the previous year.

Recommended Articles

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

  • VBA Case Statement Examples
  • Weekday in VBA
  • VBA Select Case
  • DateSerial in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA DateAdd Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More