How to use DateAdd function in VBA?

The DateAdd function in VBA (Visual Basic for Applications) is used to add a specified time interval to a date, returning a new date. This function is particularly useful for calculations involving dates, such as finding a date a certain number of days, months, or years in the future or past. The syntax for the DateAdd function is:

VBA

DateAdd(interval, number, date)

Interval: A string expression that represents the time interval you want to add. Common intervals include “yyyy” for Year, “q” for Quarter, “m” for Month, “y”, “d”, or “w” for Day, “ww” for Week, “h” for Hour, “n” for Minute, and “s” for Second.
number: A numeric expression representing the number of intervals you want to add. This can be positive (to get future dates) or negative (to get past dates).
date: The date to which the interval is added. This can be a date literal or a date variable.

Example Usage

VBA

Dim futureDate As Date
Dim startDate As Date

startDate = DateSerial(2024, 1, 1) ' January 1, 2024

' Add 10 days to the start date
futureDate = DateAdd("d", 10, startDate)
' futureDate is now January 11, 2024

' Subtract 5 months from the start date
Dim pastDate As Date
pastDate = DateAdd("m", -5, startDate)
' pastDate is now August 1, 2023

In this example:

10 days are added to January 1, 2024, resulting in January 11, 2024.
5 months are subtracted from January 1, 2024, giving August 1, 2023.

Points to Remember:

The DateAdd function is very useful for date arithmetic in VBA.
The interval parameter should be provided as a string and it is case insensitive (e.g., “d” or “D” for days).
If the calculation results in an invalid date (like adding one month to January 31st), VBA automatically adjusts the result to a valid date (in this case, to the end of February).
Be cautious with leap years and the varying number of days in months when performing calculations with months and years.

facebooklinkedin