DateAdd
Returns a Variant (Date) containing a date to which a specified time interval has been added.
Syntax: DateAdd ( interval, number, date )
- interval
- required String expression that is the interval of time to add. See Interval settings.
- number
- required Numeric expression for the number of intervals 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.
Interval 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 |
To add days to date, use Day of Year (“y”), Day (“d”), or Weekday (“w”).
Note
When the “w” interval is used to add days to a date, DateAdd adds the total number of days specified, not just workdays (Monday through Friday).
DateAdd won’t return an invalid date. The first two lines of the following example add one month to January 31:
Debug.Print Format$(DateAdd("m", 1, #1/31/1995#), "yyyy-mm-dd") ' 1995-02-28
Debug.Print Format$(DateAdd("m", 1, #1/31/1996#), "yyyy-mm-dd") ' 1996-02-29
Debug.Print Format$(DateAdd("m", -1, #3/31/2026#), "yyyy-mm-dd") ' 2026-02-28
Debug.Print Format$(DateAdd("yyyy", 1, #2/29/2024#), "yyyy-mm-dd") ' 2025-02-28
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. Subtracting a month, and adding or subtracting a quarter or a year, stops at the last day of the shorter month in the same way, as the last two lines show.
Warning BETA 1034
DateAdd with the “yyyy”, “q” or “m” interval raises no error when the result would be before January 1, 100, and returns a date in the 1900s or 2000s instead: DateAdd("yyyy", -1, #1/1/100#) returns January 1, 1999, and DateAdd("m", -1, #1/1/100#) returns December 1, 1999. VB6 raises error 5.
If number isn’t a Long value, it is rounded to the nearest whole number before being evaluated.
The format of the return value is determined by Control Panel settings, not by the format passed in the date argument.
If the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri.
Common Errors
| Error | When |
|---|---|
5 (&H5), Invalid procedure call or argument | interval is not one of the settings listed the result would be after December 31, 9999, or before January 1, 100 for an interval other than “yyyy”, “q” or “m” |
6 (&H6), Overflow | date is a number outside the range of dates |
13 (&HD), Type mismatch | date is a string that is not a date |
94 (&H5E), Invalid use of Null | interval or number is Null |
Note BETA 1034
When the result with an interval of “yyyy”, “q” or “m” would be before January 1, 100, VB6 and 64-bit VBA raise error 5 (&H5), Invalid procedure call or argument; twinBASIC raises none.
Note BETA 1034
When date is a number outside the range of dates, VB6 and 64-bit VBA raise error 13 (&HD), Type mismatch, instead.
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
Dim IntervalType As String
Dim Number As Integer
IntervalType = "m" ' "m" specifies months as interval.
FirstDate = InputBox("Enter a date")
Number = InputBox("Enter number of months to add")
MsgBox "New date: " & DateAdd(IntervalType, Number, FirstDate)