| DateAdd Function |
Named Arguments
Yes
Syntax
DateAdd(interval, number, date)
interval
Use: Required
Data Type: String
An expression denoting the interval of time you need to add or subtract (see the table Section 7.52.3).
number
Use: Required
Data Type: Numeric
An expression denoting the number of time intervals you want to add or subtract.
date
Use: Required
Data Type: Date Variant
A Variant of subtype Date or a literal denoting the date on which to base the DateAdd calculation.
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 |
Return Value
A Variant of subtype Date.
Description
Returns a variant of subtype Date representing the result of adding or subtracting a given number of time periods to or from a given date. For instance, you can calculate the date 178 months before today's date, or the date and time 12,789 minutes from now.
Rules at a Glance
Specify the interval value as a string enclosed in quotation marks (e.g., "ww").
If number is positive, the result is in the future; if number is negative, the result is in the past. (The meaning of "future" and "past" here is relative to date.)
The DateAdd function has a built-in calendar algorithm to prevent it returning an invalid date. For example, you can add 10 minutes to 31 December 1999 23:55, and DateAdd automatically recalculates all elements of the date to return a valid date, in this case, 1 January 2000 00:05. This includes leap years: the calendar algorithm takes the presence of 29 February into account for leap years.
Example
Dim lNoOfIntervals as Long
lNoOfIntervals = 100
Msgbox DateAdd("d", lNoOfIntervals, Now)Programming Tips and Gotchas
When working with dates, always check that a date is valid using the IsDate function prior to passing it as a parameter to the function.
To add a number of days to date, use either the day of the year "y", the day "d", or the weekday "w".
Both the Date data type and the Variant date subtype can handle dates only as far back as 100 A.D. DateAdd generates an error (runtime error number 5, "Invalid procedure call or argument") if the result precedes the year 100.
Both the Date data type and the Variant date subtype can handle dates as far into the future as 9999 A.D.—from a practical application standpoint, a virtual infinity. If the result of DateAdd is a year beyond 9999 A.D., the function generates runtime error number 5, "Invalid procedure call or argument."
If number contains a fractional value, it's rounded to the nearest whole number before being used in the calculation.