Access VBA Date Functions: Dates, Times, and Examples

VBA & macros · Updated November 11, 2026

Access VBA provides built-in date and time functions for getting the current date, creating date values, adding or comparing intervals, and extracting date parts. The most useful starting points are Date(), Now(), DateAdd, DateDiff, DateSerial, and Format; which one to use depends on whether you need a value for calculation or a string for display.

How dates work in Access VBA

VBA’s Date data type represents a date and time together. Access stores ordinary Date/Time values as an eight-byte floating-point number: the whole-number portion represents the date, and the fractional portion represents the time. That is why a date-only value can still be used in time calculations, and why two values that look like the same date may compare differently if one includes a time.

For example, a field displayed as 2 November 2026 might actually contain 2 November 2026 at 3:45 p.m. A comparison against midnight on that date will not match that value exactly. Access can format a value to hide its time, but formatting does not remove the time stored in it.

In Access, date functions can appear in VBA procedures, form and report expressions, and queries. The surrounding syntax can vary: VBA code uses function calls and variables, while a query may use functions directly in an expression such as DateAdd("d", 7, [OrderDate]). If a query’s expression builder or a form control uses a function, it is still important to understand whether the result is a date value or just formatted text.

Current date and current date-time

Use Date() when you want the computer’s current date with the time set to midnight. Use Now() when you need the current date and time. Time() returns the current time, represented as a date/time value whose date portion is the base date. These functions read the computer’s system clock; they do not retrieve a date from an Access table or a server clock.

  • Date(): useful for a date-only default, such as a record’s entry date.
  • Now(): useful for a timestamp that records when an event occurred.
  • Time(): useful when only the current time is required, although storing a complete timestamp is often more informative.

For instance, a table field’s Default Value can be set to Date() to supply today’s date when a record is created, or to Now() to capture the date and time. A default is not the same as a rule that continually updates: it supplies a value when a new record is created, rather than automatically changing the value every day.

Creating and converting date values

DateSerial(year, month, day) constructs a date from numeric components and is often safer than assembling a date from text. It also handles out-of-range month and day values by carrying them into adjacent months or years. For example, using month 13 advances into the following year, and day 0 refers to the last day of the preceding month. This behavior can be useful for calculations, but validate input first if an out-of-range value should count as an error.

To build the first day of the current month, use DateSerial(Year(Date()), Month(Date()), 1). To get the last day of that month, construct the first day of the next month and subtract one day: DateSerial(Year(Date()), Month(Date()) + 1, 0). These expressions also work across December because DateSerial normalizes the month value.

DateValue(expression) converts text that Access recognizes as a date into a date value, while CDate(expression) converts a value to the Date subtype. Both can be affected by regional settings when the input is ambiguous. A string such as 03/04/2026 can mean March 4 or April 3 depending on the date convention in use. For data entry, validate the input and prefer numeric components with DateSerial when possible. IsDate(expression) can test whether an expression is recognized as a date, but it does not resolve what an ambiguous date string was intended to mean.

Adding and subtracting intervals with DateAdd

DateAdd(interval, number, date) returns a date after adding an interval. Use a positive number to move forward and a negative number to move backward. Common interval codes include yyyy for year, q for quarter, m for month, y for day of year, d for day, w for weekday, ww for week, and h, n, and s for hour, minute, and second.

One frequent source of mistakes is the interval code for minutes: it is n, not m. The latter means month. For a date seven days after a value in a field named DueDate, an Access expression can be DateAdd("d", 7, [DueDate]). In VBA, the same function can calculate a reminder date. Adding one month is not always equivalent to adding a fixed number of days: month lengths differ, so use the interval that matches the business meaning of the calculation.

Month-end behavior deserves particular attention. When adding a month to a day that does not exist in the destination month, the result is adjusted to a valid date. For recurring schedules, test dates near the end of a month, including leap-year February, to make sure the result matches the intended rule. “One month later” and “30 days later” are different requirements.

Measuring differences with DateDiff

DateDiff(interval, date1, date2) returns the number of specified interval boundaries between two date values. Its result is not necessarily the elapsed duration expressed in whole units. For example, DateDiff("d", startValue, endValue) counts day boundaries; it does not calculate a precise number of completed 24-hour periods. Similarly, counting month boundaries does not tell you how many full months have elapsed.

That distinction matters when calculating ages, service periods, or overdue time. A basic year difference can be one too high before a person’s birthday in the current year. A practical age calculation starts with DateDiff("yyyy", birthDate, Date()), then subtracts one if this year’s birthday has not yet occurred. The birthday comparison must account for the actual month and day, including how an application chooses to handle a February 29 birthday in a non-leap year.

For week-based calculations, DateDiff accepts optional settings for the first day of the week and the first week of the year. The defaults can depend on system or calendar settings. Specify these options when results must follow a particular reporting convention, and test dates at year boundaries. A week number is not universally defined without a rule for which day starts the week and how the first week is determined.

Extracting parts and formatting output

Use Year, Month, and Day to obtain a date’s calendar components. Hour, Minute, and Second extract time components. DatePart can retrieve a chosen part, such as a quarter or week number, while Weekday returns a number for the day of the week. WeekdayName and MonthName return readable names; their output can depend on the chosen settings and locale.

Format(value, formatString) produces a formatted string, for example Format(Date(), "yyyy-mm-dd"). Use it for labels, reports, or export layouts—not as a substitute for a date value when you still need date arithmetic or chronological sorting. Text dates may sort alphabetically, so a sequence such as 01/10/2026 and 12/02/2025 can appear in an order that is not chronological. Keep dates as dates for storage and calculation, and apply formatting at the display stage.

A short VBA example

This procedure shows how to calculate a follow-up date 14 days after a supplied date and display it. The variables are declared explicitly, and the calculated value remains a Date rather than being converted to text until it is displayed.

Option Explicit

Public Sub ShowFollowUpDate()
    Dim startDate As Date
    Dim followUpDate As Date

    ' Use today's date as an example starting point.
    startDate = Date()
    followUpDate = DateAdd("d", 14, startDate)

    MsgBox "Follow-up date: " & Format(followUpDate, "mmmm d, yyyy")
End Sub

Before running any macro, save a copy of the database or workbook. Macro actions generally cannot be reversed with Ctrl+Z, so test procedures that change records on a backup or a small sample first. This example only displays a message and does not edit records.

Common date problems and how to avoid them

  • Ambiguous text: Avoid relying on a slash-formatted date string when users or machines may use different regional conventions. Build dates from validated year, month, and day numbers or use properly typed date parameters.
  • Hidden time portions: A field may display only the date while retaining a time. For a whole-day query, use a range: include values at or after the start of the day and before the start of the next day. This approach also handles records with different times during the day.
  • Null values: A Null is not the same as zero or an empty string. Date calculations involving Null generally produce Null, so check for Null before using a field in a calculation. In VBA, use IsNull for this purpose.
  • Wrong interval: Remember that m means month and n means minute in DateAdd and DateDiff interval arguments.
  • Display mistaken for stored value: Changing a field’s Format property changes how a date appears, not the underlying value. Conversely, Format() returns text and should not be used where a Date is needed for later calculations.

Choosing the right function

Start by deciding what kind of result the next operation needs. Use Date or Now to read the current system date or timestamp; DateSerial to construct a reliable date from components; DateAdd to move a date by a calendar interval; and DateDiff to count interval boundaries. Choose Year, Month, Day, or DatePart to inspect a component, and reserve Format for presentation.

In a table, keep date fields stored with the Date/Time data type instead of storing date-shaped text. In queries, use typed parameters where practical, especially when filtering user-entered dates. For a day-long filter, a start-inclusive and next-day-exclusive range is generally more reliable than equality against a date that may have a time component. If an application uses different regional settings or reports by weeks, document the convention and set the relevant options explicitly.

Frequently Asked Questions

What is the difference between Date() and Now() in Access VBA?
Date() returns the current date with no non-midnight time component. Now() returns the current date and time. Use Date for a date-only value and Now for a timestamp.

How do I add days to a date in Access VBA?
Use DateAdd("d", numberOfDays, dateValue). For example, adding 7 with the interval d returns a date seven days later. Use n, not m, when adding minutes.

How do I get the first day of the month?
Use DateSerial(Year(dateValue), Month(dateValue), 1). It constructs the first day of the month containing dateValue, without depending on how a date string is interpreted.

Why does DateDiff return an unexpected age or duration?
DateDiff counts interval boundaries, not necessarily completed periods. For an age in years, compare the birthday in the current year and subtract one if it has not happened yet. For elapsed hours or days, consider the actual date-time values and the required rounding rule.

Does Format() change the date stored in a field?
No. Format() returns a text representation for display. It does not change the stored date, and converting dates to text can interfere with sorting and calculations. Keep the value as a date until it needs to be displayed.

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.