Returns a Variant containing a date to which a specified time interval has been added.
DateAdd(interval, number, date)
The DateAdd function syntax has these named arguments:
| Part | Description |
| interval | String expression, as described in Settings, that is the interval of time you want to add. |
| number | 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 | Date being added to, or the name of a Variant containing a valid date. |
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 |
You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you could use DateAdd to calculate a date 30 days from today or a time that is 45 minutes from now.
If you want to add days to date, you can use Day of Year (“y”), Day (“d”), or Weekday (“w”).
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.
DateDiff Function, DatePart Function, Day Function, Format Function, Now Function, Weekday Function, Year Function.
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.IntervalType As StringNumber As IntegerMsg= "m" ' "m" specifies months as interval.= InputBox("Enter a date")= InputBox("Enter number of months to add")= "New date: " & DateAdd(IntervalType, Number, FirstDate)Msg
The following example uses DateAdd to find the number of days in a month. The function DaysInMonth takes an argument of type Date.
Function DaysInMonth(dteAny As Date) As Integer
Dim dteNextMonth, dteEndOfMonth
dteNextMonth = DateAdd("m", 1, dteAny)
dteEndOfMonth = dteNextMonth - DatePart("d", dteNextMonth)
DaysInMonth = DatePart("d", dteEndOfMonth)Function
The next example shows how you can create a calculated control using the DateAdd function to display the date by which a particular order must be shipped — in this case, thirty days after the OrderDate. Suppose you have a form based on an Orders table, with a field called OrderDate. You can create another text box on the form to display the shipping date by setting the ControlSource property of this text box as in the following example.
= DateAdd("d", 30, [OrderDate])