How to manipulate dates using the DateAdd function in Excel VBA.
DateAdd function
Returns a Variant (Date) containing a date to which a specified time interval has been added.
Syntax
DateAdd(interval, number, date)
The DateAdd function syntax has the following named arguments:
Item | Description |
interval | This argument is required. It is a string expression that is the interval of time you want to add. |
number | This argument is required. It is a numeric expression that is the number of intervals you want to add. It can be positive or negative. We use a positive number to get dates in the future. Negative numbers are used to get dates in the past. |
date | This argument is required. It is a Variant (Date) data type or literal. It represents the date to which the interval is added. |
The macro code below shows a few examples of dateadd function. Only one line is active and the other lines have been commented out using an apostrophe:
Private Sub CommandButton1_Click()
Me.TextBox2.Value = DateAdd(“yyyy”, 5, Me.TextBox1.Value)
‘Me.TextBox2.Value = DateAdd(“m”, 2, Me.TextBox1.Value)
‘Me.TextBox2.Value = DateAdd(“d”, 2, Me.TextBox1.Value)
End Sub
From the above VBA code we can easily see that years, months or days can be added to our date.
The interval argument has these terms:
Term | Description |
yyyy | Year |
q | Quarter |
m | Month |
y | Day of year |
d | Day |
w | Weekday |
ww | Week |
h | Hour |
n | Minute |
s | Second |
Further reading: