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 these named arguments:
| Part | Description |
|---|---|
| interval | Required. String expression that is the interval of time you want to add. |
| number | Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past). |
| date | Required. Variant (Date) or literal representing the date to which the interval is added. |
Settings
The interval argument has these settings:
| Setting | Description |
|---|---|
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
Remarks
Use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.
To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").
Note
When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect.
The DateAdd function won't return an invalid date. The following example adds one month to January 31:
DateAdd("m", 1, "31-Jan-95")
In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.
If the calculated date would precede the year 100 (that is, you subtract more years than are in date), an error occurs.
If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.
Note
The format of the return value for DateAdd is determined by Control Panel settings, not by the format that is passed in the date argument.
Note
For date, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri. If month values are names, the name must be consistent with the current Calendar property setting. To minimize the possibility of month names conflicting with the current Calendar property setting, enter numeric month values (Short Date format).
Example
This example takes a date and, using the DateAdd function, displays a corresponding date a specified number of months in the future.
Dim FirstDate As Date ' Declare variables.
Dim IntervalType As String
Dim Number As Integer
Dim Msg As String
IntervalType = "m" ' "m" specifies months as interval.
FirstDate = InputBox("Enter a date")
Number = InputBox("Enter number of months to add")
Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)
MsgBox Msg
See also
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.