Welcome to Excel Avon
DateAdd Function in Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
So, as you know VBA DateAdd is a function that does addition or subtraction of time/date intervals. Today’s Post How to Use the DateAdd Function in Excel
The DateAdd function is a very useful function in VBA. It is very difficult to find out the date and time when you do some calculations on it. But in our daily work, it is an essential type of data that we can use. Comparison, addition, subtraction between different dates is some of the familiar operations that we perform.
The syntax for this function takes three arguments: interval, number, and date.
DateAdd(Interval as String, Number as Double, Date)
Interval – Interval is what kind of value you want to add or subtract. For example, whether you want to add a hours, whether you want to add or subtract month, whether you want to add or subtract a quarter, etc.
Number- 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 on which you want to Add/Subtract the interval.
| Interval Type | Description |
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of the year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
To understand for DateAdd Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
DateAdd Function for Years
To understand DateAdd Function in Excel VBA, we will write a subroutine for DateAdd Function in Excel VBA after inserting the module in VBE.
Sub UseDateAdd() End Sub
After writing the subroutine we will define the variable
Sub UseDateAdd() Dim CurrentDate as Date Dim Result as Date End Sub
Now I will Store time as Now in CurrentDate
Sub UseDateAdd() Dim CurrentDate as Date Dim Result as Date Currentdate = Now End Sub
Use debug. Print for printing CurrentDate
Sub UseDateAdd() Dim CurrentDate as Date Dim Result as Date Currentdate = Now debug.Print "Current Date" & currentdate End Sub
Use Dateadd find the same date after two years from Current date Again debug.Print Result
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("yyyy", 2, currentdate)
debug.Print "Result" & Result
End Sub
Click Run button,
Now you can see that we have used the value after 2 years and now the result is printed in the Immediate window.
DateAdd Function for Quarter
q is the value of quarter write the value for quarter and We will find the date by adding 2 quarters 1 quarter = 3 months
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("q", 2, currentdate)
End Sub
Again debug.Print Result
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("q", 2, currentdate)
debug.Print "Result" & Result
End Sub
Click Run button, now you can see that we have used the value after 2 quarter and now the result is printed in the Immediate window.
DateAdd Function For Day
‘d’ is the value of Day now we will write the value for the Day and add 20 Days from currentdate.
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("d", 20, currentdate)
End Sub
Use debug.print for printing result
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("d", 20, currentdate)
debug.Print "Result" & Result
End Sub
we keep watching in the immediate window and get the result by adding 20 days to the current date.
DateAdd Function For Week
“ww” is the value of week now we will write the value for the week and add 1 week from currentdate.
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("ww", 1, currentdate)
End Sub
After clicking the run button, we keep watching in the immediate window and get the result by adding 1 week to the current date.
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("ww", 1, currentdate)
debug.Print "Result" & Result
End Sub
we keep watching in the immediate window and get the result by adding 20 days to the current date.
DateAdd Function For Hours
‘h’ value is used for hours now
Sub UseDateAdd() Dim CurrentDate as Date Dim Result as Date Currentdate = Now debug.Print "Current Date" & currentdate End Sub
Use dateadd function and debug.Print for print result
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("h", 1, currentdate)
debug.Print "Result" & Result
End Sub
After clicking the run button, we keep looking in the immediate window and get the result by adding 1 hours to the current date.
DateAdd Function in Excel For Minute
‘n’ value is used for minute now we will print result add 20 minutes from current time.
Sub UseDateAdd() Dim CurrentDate as Date Dim Result as Date Currentdate = Now debug.Print "Current Date" & currentdate End Sub
Use dateadd function with ‘n’ value and debug.Print for print result.
Sub UseDateAdd()
Dim CurrentDate as Date
Dim Result as Date
Currentdate = Now
debug.Print "Current Date" & currentdate
Result = dateAdd("n", 20, currentdate)
debug.Print "Result" & Result End Sub
End Sub
After clicking the run button, we keep looking in the immediate window and get the result by adding 20 minutes to the current time.
So, I hope you have understood How to use DateAdd Function in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about DateAdd Function in Excel VBA
