Excel VBA DateAdd Function
Using the DateAdd function, we can add and subtract days, months, and years from the given date. The date in Excel is part and parcel of our daily work. We cannot work in Excel without date, time, and other important stuff. The common process is adding one date to another and subtracting one date from another. In the regular worksheet, we do arithmetic operations and get the result. In VBA, we have the function called DateAdd, which will do the job.
You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA DateAdd Function (wallstreetmojo.com)
Syntax
Interval: Interval is nothing but what 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 a quarter, etc.
Below is a list of codes and descriptions.
- Number: The number of months, days, or weeks (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?
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.
Step 2: Define the variable as Date.
Code:
Sub DateAdd_Example1()
Dim Month As Date
End Sub
Step 3: For this variable, assign a value.
Sub DateAdd_Example1() Dim NewDate As Date
NewDate =
End Sub
Step 4: Start the DateAdd formula.
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.”
Sub DateAdd_Example1() Dim NewDate As Date
NewDate = DateAdd(“d”,
End Sub
Step 6: How many days do we need to add? We have to add 5 days.
Sub DateAdd_Example1() Dim NewDate As Date
NewDate = DateAdd(“d”, 5,
End Sub
Step 7: Our date is “14-03-2019.”
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 boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.
Sub DateAdd_Example1() Dim NewDate As Date
NewDate = DateAdd(“d”, 5, “14-03-2019”) MsgBox NewDate
End Sub
If we run this code, we should get the date as 19-03-2019.
The system’s date format is “mm-dd-yyyy,” so the result shows as per the system date settings.
However, we can modify this by applying the VBA FORMATVBA FORMATThe format function in VBA is used to format the given values into the format that is desired. It can be used to format dates, numbers, and trigonometrical values, among other things.read more function.
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 be like this: “19-Mar-2019.”
Example #2 – Add Months
To add months, below is the 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 using DateAdd, use the below 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.
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
Example #5 – Add Weekday
To add weekdays, below is the 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
Example #6 – Add Week
To add a week, below is the 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
Example #7 – Add Hour
To add the hour, below is the 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
Example #8 – Subtract Date
To add, we have supplied positive numbers. To subtract, we need to supply negative numbers that are all.
To subtract 3 months from the supplied date, below is the 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:
We deduct 3 months from the date 14th March 2019. So, it will return to the previous year.
Recommended Articles
This article is a guide to the VBA DateAdd Function. Here, we learn how to use the VBA DateAdd function to add and subtract days, months, and 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 ExamplesWeekday in VBAVBA Select CaseDateSerial in VBA
