VBA DateAdd Function

DateAdd Description

The VBA DateAdd Function allows you to add (or subtract) days, months, years, hours, quarters, etc. to Dates or Times.

Simple DateAdd Examples

Here is a simple DateAdd example:

Sub DateAdd_Day()
    MsgBox DateAdd("d", 20, #4/1/2021#)
End Sub

This code will add 20 days (indicated by “d”) to the date 4/1/2021:

Instead, we can change the Interval argument from “d” to “m” to add 20 months to the date 4/1/2021:

Sub DateAdd_Month()
    MsgBox DateAdd("m", 20, #4/1/2021#)
End Sub

Instead of displaying the date in a message box, we can assign it to a variable:

Sub DateAdd_Day2()
    Dim dt as Date    
    dt = DateAdd("d", 20, #4/1/2021#)

    MsgBox dt
End Sub

DateAdd Syntax

In the VBA Editor, you can type  “DateAdd(” to see the syntax for the DateAdd Function:

The DateAdd function contains 3 arguments:

Interval: Time unit (Days, Months, Years, etc.). Enter as string. (ex. “m” for Month)

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of Year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

Number: Numeric value representing the number of time units to add. (ex. 20 to add 20 units)

Date: Initial Date. See next section.

Examples of Excel VBA DateAdd Function

Referencing Dates

To start, we will demonstrate different ways to reference dates using the VBA DateAdd Function.

Each of these DateAdd functions produce the same result:

Sub DateAdd_ReferenceDates()

    MsgBox DateAdd("m", 2, #4/1/2021#)

    MsgBox DateAdd("m", 2, DateSerial(2021, 4, 1))

    MsgBox DateAdd("m", 2, DateValue("April 1, 2021"))

End Sub

Or you can reference a cell containing a date:

Sub DateAdd_ReferenceDates_Cell()

    MsgBox DateAdd("m", 2, Range("C2").Value)

End Sub

Or create and reference a date variable:

Sub DateAdd_Variable()

    Dim dt As Date
    dt = #4/1/2021#

    MsgBox DateAdd("m", 2, dt)

End Sub

Add or Subtract Dates

We have already shown you how to add to a date:

Sub DateAdd_Day2()
    Dim dt as Date    
    dt = DateAdd("d", 20, #4/1/2021#)

    MsgBox dt
End Sub

You can subtract from dates by using a negative number (ex. -20 instead of 20):

Sub DateAdd_Day()
    Dim dt as Date    
    dt = DateAdd("d", -20, #4/1/2021#)

    MsgBox dt
End Sub

Adding Different Units of Time

Years

Sub DateAdd_Years()
    MsgBox DateAdd("yyyy", 4, #4/1/2021#)
End Sub

Quarter

Sub DateAdd_Quarters()
    MsgBox DateAdd("q", 2, #4/1/2021#)
End Sub

Month

Sub DateAdd_Months()
    MsgBox DateAdd("m", 2, #4/1/2021#)
End Sub

Day of Year

Sub DateAdd_DaysofYear()
    MsgBox DateAdd("y", 2, #4/1/2021#)
End Sub

Day

Sub DateAdd_Days3()
    MsgBox DateAdd("d", 2, #4/1/2021#)
End Sub

Weekday

Sub DateAdd_Weekdays()
    MsgBox DateAdd("w", 2, #4/1/2021#)
End Sub

Week

Sub DateAdd_Weeks()
    MsgBox DateAdd("ww", 2, #4/1/2021#)
End Sub

Add to Today

These examples will add units of time to today using the Date Function.

Sub DateAdd_Year_Test()
    Dim dtToday As Date
    Dim dtLater As Date
    
    dtToday = Date
    dtLater = DateAdd("yyyy", 1, dtToday)
    
    MsgBox "A year later is " & dtLater
End Sub

Sub DateAdd_Quarter_Test()
    MsgBox "2 quarters later is " & DateAdd("q", 2, Date)
End Sub

 

Adding and Subtracting Time

The DateAdd function also works with Times.  Here are a few examples of adding (or subtracting) time to a time:

Hour

This example will add 2 hours to a time:

Sub DateAdd_Hour()
    MsgBox DateAdd("h", 2, #4/1/2021 6:00:00#)
End Sub

Minute

This example will subtract 120 minutes from the Current Time:

Sub DateAdd_Minute_Subtract()
    MsgBox DateAdd("n", -120, Now)
End Sub

Second

Sub DateAdd_Second()
    MsgBox DateAdd("s", 2, #4/1/2021 6:00:00#)
End Sub

Formatting Dates

When dates (or times) are displayed in Excel, UserForms, or Messageboxes, you should indicate how the dates should be displayed by using the Format Function.  We’ve included a few examples below:

Sub FormattingDatesTimes()

    'Returns Current Date and Time
    dt = Now()
    
    'ex. 07/02/2021
    Range("B2") = Format(dt, "mm/dd/yyyy")
    
    'ex. July 2, 2021
    Range("B3") = Format(dt, "mmmm d, yyyy")
    
    'ex. July 2, 2021 09:10
    Range("B4") = Format(dt, "mm/dd/yyyy hh:mm")
    
    'ex. 7.2.21 9:10 AM
    Range("B5") = Format(dt, "m.d.yy h:mm AM/PM")

End Sub