Access VBA DateAdd: Add or Subtract Dates and Times

VBA & macros · Updated November 11, 2026

In Access VBA, use DateAdd(interval, number, date) to add or subtract a time interval from a date or time; for example, DateAdd("d", 7, Date) returns the date seven days from today. Use a negative number to go backward, and choose an interval such as "m" for months or "h" for hours.

DateAdd syntax and what it returns

The built-in VBA function has three arguments: the interval to change, the number of those intervals, and the starting date. Access returns a date/time value, so the result can be assigned to a Date variable, displayed in a control, or used in a query expression. The function calculates a value; it does not automatically save that value into a table or change a form control.

For example, DateAdd("d", 10, #6/5/2026#) represents June 15, 2026. The example uses a date literal for illustration; in an application, it is usually better to pass an actual Date value from a field, control, or variable instead of relying on text that might be interpreted differently under another regional date format.

The argument called number can be positive or negative. A positive value moves forward in time, while a negative value moves backward. The starting value can include a time as well as a date, and DateAdd generally preserves that time portion when it changes the date. For example, adding one day to a value representing 9:30 AM produces the following day’s value at 9:30 AM.

Choosing the interval

The interval is a short text code. Use the code that matches the unit you mean; in particular, do not use "m" when you mean minutes. In DateAdd, "m" means month and "n" means minute.

  • "yyyy": year
  • "q": quarter (three months)
  • "m": month
  • "y", "d", or "w": day-related intervals
  • "ww": week
  • "h": hour
  • "n": minute
  • "s": second

For ordinary calendar-day arithmetic, "d" is the clearest choice. "ww" is useful when the intended unit is a seven-day week. The names "y" (day of year) and "w" (weekday) can be confusing: they are not instructions to skip weekends or apply a holiday calendar. If a deadline must land on a working day, DateAdd alone does not provide that business-calendar rule. You need additional logic based on the weekends and holidays relevant to the application.

Common VBA examples

These examples show typical uses in Access code. The variables are declared explicitly so the expected value types are visible.

Dim appointmentTime As Date
Dim reminderTime As Date

' Move an appointment one hour later.
appointmentTime = #6/5/2026 9:30:00 AM#
reminderTime = DateAdd("h", 1, appointmentTime)

' Print the result in the Immediate window.
Debug.Print Format$(reminderTime, "yyyy-mm-dd hh:nn")

The result in this example is June 5, 2026 at 10:30 AM. In VBA formatting strings, nn represents minutes; using mm in a time format can be confusing because mm is also used for months. Format$ affects how a value is displayed as text, not the underlying Date value.

To calculate a date a set number of days from the current system date, assign the result to a Date variable:

Dim followUpDate As Date

' Schedule a follow-up 14 days from today.
followUpDate = DateAdd("d", 14, Date)
Debug.Print Format$(followUpDate, "yyyy-mm-dd")

Here, Date supplies the current date without a time component. Use Now instead when the current time should be part of the starting value. The result from DateAdd is based on the value passed in; it does not itself decide whether a date is a business day or whether a deadline should be adjusted for a holiday.

Months, years, and month-end dates

Adding a month is not the same as adding a fixed number of days. Months have different lengths, so DateAdd("m", 1, startDate) advances by one calendar month. If the original day number does not exist in the destination month, Access returns the last valid day of that month. For example, adding one month to January 31, 2026 gives February 28, 2026. In a leap year, the corresponding February result can be February 29.

This behavior matters for recurring dates. If a process begins on the 31st and should run monthly, repeatedly adding one month to the previous result may cause later dates to remain near the end of the month. Decide whether the requirement is “one calendar month after the previous occurrence,” “the final day of each month,” or “the same numbered day when possible.” Those rules can produce different schedules and may require separate logic.

Year additions have a related edge case. Adding one year to February 29 in a leap year results in a valid date in the following year; because that year may not have February 29, the date becomes February 28. If an application has a special rule for birthdays, contract anniversaries, or renewal dates, encode that rule explicitly rather than assuming every calendar anniversary is identical.

Using DateAdd in an Access procedure

You can wrap DateAdd in a function when the same calculation is needed in several places. This example accepts a Date and a whole number of days and returns the calculated Date:

Public Function AddDaysToDate(ByVal startDate As Date, _
                              ByVal daysToAdd As Long) As Date
    ' Return the date shifted by the requested number of days.
    AddDaysToDate = DateAdd("d", daysToAdd, startDate)
End Function

Call the function from VBA with valid Date and Long values. If the source can be Null, do not pass it directly to a parameter declared As Date: a Date variable cannot hold Null. Check for Null first, or accept a Variant and handle Null deliberately. For example:

Public Function AddDaysIfPresent(ByVal sourceValue As Variant, _
                                 ByVal daysToAdd As Long) As Variant
    ' Preserve Null instead of converting a missing date.
    If IsNull(sourceValue) Then
        AddDaysIfPresent = Null
    Else
        AddDaysIfPresent = DateAdd("d", daysToAdd, CDate(sourceValue))
    End If
End Function

Before running a macro or procedure that changes records, save a copy of the database or make another suitable backup. VBA macros cannot be undone with Ctrl+Z. The examples above calculate or print values; if you adapt code to update records, test it on a copy and confirm the target records before running it on important data.

DateAdd in queries, forms, and reports

DateAdd is also available in Access query expressions. To show a calculated date 30 days after each record’s order date, add a calculated field to a query such as:

FollowUpDate: DateAdd("d", 30, [OrderDate])

Replace [OrderDate] with the name of the Date/Time field in the query. This expression produces a calculated column; it does not write the new date back to the table. To store a value, use an appropriate update operation and take care to limit the records affected. A form or report can use a similar expression in a control’s Control Source, or VBA can assign a DateAdd result to a control’s Value property.

If the source field is Null, the result is Null as well. That can be appropriate when no order date exists, but it may affect sorting, filtering, or other expressions. If missing dates should follow a particular rule, make the rule explicit rather than silently substituting today’s date. Also verify that the source field is actually a Date/Time field; text that looks like a date can be interpreted inconsistently or fail conversion.

DateAdd compared with related date functions

DateAdd answers “what date or time results after shifting this value by a specified interval?” DateDiff answers a different question: it calculates the number of specified intervals between two dates. For example, use DateAdd to find a due date 30 days after an event, and DateDiff when you need to compare two dates in terms of days, months, or another interval.

DateSerial is useful when you need to construct a date from year, month, and day components. For instance, it can help create the first day of a selected month. It is not a substitute for adding a relative interval to an existing date. Similarly, Date returns the current system date, while Now returns the current date and time; either can be the starting argument to DateAdd.

Choose based on the question your code needs to answer. If you need “three months after the entered start date,” use DateAdd with "m". If you need “how many days separate these two dates,” use DateDiff with "d". Keeping those jobs distinct makes expressions easier to review and reduces errors caused by treating a calendar interval as a fixed number of days.

Common problems and how to avoid them

  • Using the wrong minute code: "m" adds months; use "n" for minutes.
  • Passing text instead of a date: Prefer a Date/Time field, Date variable, or a value produced by a date function. Avoid ambiguous text dates such as 03/04/2026 when the regional order is uncertain.
  • Assigning Null to a Date variable: Test with IsNull before assigning or converting the value.
  • Expecting workday scheduling: DateAdd does not consult a holiday table and is not a complete working-day calculator.
  • Expecting the table to change: A calculated query column or a VBA assignment to a temporary variable does not update a stored field by itself.
  • Overlooking month ends: Calendar months differ in length, so check dates such as the 29th, 30th, and 31st against the application’s rules.

When a result looks wrong, inspect the input value, the interval string, and the number separately. Use Debug.Print to display the source and result in the Immediate window, and temporarily format them in an unambiguous year-month-day order. This helps distinguish a calculation problem from a display-format issue.

Frequently Asked Questions

How do I add 30 days to a date in Access VBA?
Use DateAdd("d", 30, startDate), replacing startDate with a Date variable or other valid date expression. The positive number moves the result 30 days forward.

How do I subtract days with DateAdd?
Pass a negative number, such as DateAdd("d", -7, startDate), to return the date seven days earlier. The same approach works with other intervals, such as months or hours.

Does DateAdd(“m”, 1, myDate) add 30 days?
No. It advances one calendar month, whose length depends on the destination month. A date near the end of a month can therefore produce a result fewer than 30 days later.

What is the DateAdd interval for minutes?
Use "n". The interval "m" means months, so using it for minutes can return a date far from the intended result.

Does DateAdd skip weekends and holidays?
No. DateAdd does not apply a custom work schedule or holiday list. If a result must fall on a working day, add logic that checks the application’s weekend and holiday rules.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.