What is DATEADD Function in VBA
The VBA DATEADD function is listed under the date and time category of VBA functions. When you use it in a VBA code, it returns a date or a time by adding an interval to it. In simple words, it can add seconds, minutes, hours, days, months, or years to date or time value and return that new date and time in the result.
Syntax & Arguments
Dateadd(Interval, Number, Date)
- Interval: A string to specify the interval.
- “D” – Days
- “H” – Hours
- “N” – Minutes
- “M” – Months
- “Q” – Quarters (of a Year)
- “S” – Seconds
- “ww” – Weeks
- “yyyy” – Years
- Number: This is the number of intervals you want to add (or subtract if negative) to the date. For instance, 2 in the context of months would add two months to the specified date.
- Date: This is the date on which the interval will be added. It can be a date, like #2023-01-01#, a variable containing a date, or a function returning a date, such as Now() or Date().
Adding Days/Months/Year with DATEADD
To practically understand how to use the VBA DATEADD function, you need to go through the below example where we have written a VBA code by using it:
Sub example_DATE()
Range("B1") = DateAdd("YYYY", 2, Range("A1"))
End Sub- Range(“A1”): This part of the code refers to cell A1 in the Excel worksheet. We are using it to get the result of the function in cell A1.
- DateAdd(“YYYY”, 2, Range(“A1”)): The first argument “YYYY” specifies that years should be added. The second argument 2 is the number of years to add. The third argument is the date to which the years should be added, in this case, the value in cell A1. The date we have in cell A1 is 13-Nov-2017, and in the function, we have used the interval “YYYY” to mention that we want to add a year. As a result, it returned on 13-Nov-2019 which is the exact date after the 2 years.
Subtracting Days/Months/Year with DATEADD
In the same way, you can use DATEADD to subtract days/months/years from a date. Check out the below code:
Dim myDate As Date
myDate = DateAdd("d", -10, Date())
MsgBox "10 days ago it was: " & myDate - Dim myDate As Date – This line declares a variable named myDate as a Date data type.
- myDate = DateAdd(“d”, -10, Date()) – This line is using the DateAdd function to subtract 10 days from the current date. The “d” stands for “day”, -10 is the number of days to subtract, and Date() returns the current date.
- MsgBox “10 days ago it was: ” & myDate – This line displays a message box with the text “10 days ago it was: ” followed by the calculated date.
Calculating the End of Next Quarter
Dim endOfQuarter As Date
endOfQuarter = DateAdd("q", 1, DateSerial(Year(Date()), Month(Date()) - Month(Date()) Mod 3 + 3, 0))
MsgBox "The end of next quarter is: " & endOfQuarter - Dim endQuarter As Date: This line declares a variable named endQuarter of type Date.
- DateAdd(“q”, 1, DateSerial(Year(Date), Month(Date) – Month(Date) Mod 3 + 3, 0)): This line adds 1 quarter to the date returned by the DateSerial function.
- DateSerial(Year(Date), Month(Date) – Month(Date) Mod 3 + 3, 0): This function returns a date. Year(Date) gets the current year, Month(Date) – Month(Date) Mod 3 + 3 calculates the first month of the next quarter, and 0 sets the day to the last day of the previous month, effectively getting the end of the quarter.
- MsgBox “The end of next quarter is: ” & endQuarter: This line displays a message box with the text “The end of next quarter is: ” followed by the end of the next quarter.
Notes
- Correct Interval Specification – Use the correct interval code in the function. Using an incorrect interval can lead to runtime errors.
- If the value specified is a value other than a date or a date that can’t be recognized as a date, VBA will return the run-time 13 error.
- Back to the Excel VBA / VBA Functions