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: