VBA DATEADD Function

VBA DateAdd Function

Overview of VBA DateAdd Function:


VBA DateAdd function is categorized as a Date & Time function. This is a built-in Excel VBA Function. This function returns a date after which a certain time or date interval has been added. We can use this function in VBA and can’t use in Excel. This function we use in either procedure or function in a VBA editor window in Excel. We can use this VBA DateAdd Function in any number of times in any number of procedures or functions. Let us learn what is the syntax and parameters of the DateAdd function, where we can use this DateAdd Function and real-time examples in Excel VBA.

Syntax of VBA DateAdd Function

The syntax of the DateAdd Function in VBA is

DateAdd(Interval, Number, Date)

The DateAdd function returns a string value.

Parameters or Arguments:

The DateAdd function has three arguments in Excel VBA.
where
Interval:The Interval is a required argument. It takes one of the following values.

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

Number:The Number is a required argument. It represents the number of intervals that you want to add. It can be either positive or negative value. If it is negative get past dates, otherwise for positive value get future dates.
Date:The Date is a required argument. It represents date. It specifies which the interval is added.

Where we can apply or use VBA DateAdd Function?

We can use this DateAdd Function in VBA MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Add 4 days to the date

Here is a simple example of the VBA DateAdd function. This below example adds 4 days to the date.

'Add 4 days to the date
Sub VBA_DateAdd_Function_Ex1()

    'Variable declaration
    Dim lDate As Date
    Dim oDate As Date
            
    lDate = "02/04/2018"
    oDate = DateAdd("d", 4, lDate)
        
    MsgBox "New date after adding 4 days : " & oDate, vbInformation, "VBA DateAdd Function"
    
End Sub

Output: Here is the screen shot of the first example output.
VBA DateAdd Function

Example 2: Add 3 months to the date

Let us see one more example of the VBA DateAdd function. This below example adds 3 months to the date.

'Add 3 months to the date
Sub VBA_DateAdd_Function_Ex2()

    'Variable declaration
    Dim lDate As Date
    Dim oDate As Date
            
    lDate = "02/04/2018"
    oDate = DateAdd("m", 3, lDate)
        
    MsgBox "New date after adding 3 months : " & oDate, vbInformation, "VBA DateAdd Function"
    
End Sub

Output: Here is the screen shot of the second example output.
VBA DateAdd Function

Example 3: Add 2 years to the date

Let us see another example of the VBA DateAdd function. This below example adds 2 years to the date.

'Add 2 years to the date
Sub VBA_DateAdd_Function_Ex3()

    'Variable declaration
    Dim lDate As Date
    Dim oDate As Date
            
    lDate = "02/04/2018"
    oDate = DateAdd("yyyy", 2, lDate)
        
    MsgBox "New date after adding 2 years : " & oDate, vbInformation, "VBA DateAdd Function"
    
End Sub

Output: Here is the screen shot of the third example output.
VBA DateAdd Function

Example 4: Add 1 week to the date

One more example of the VBA DateAdd function. This below example adds 1 week to the date.

'Add 1 week to the date
Sub VBA_DateAdd_Function_Ex4()

    'Variable declaration
    Dim lDate As Date
    Dim oDate As Date
            
    lDate = "02/04/2018"
    oDate = DateAdd("ww", 1, lDate)
        
    MsgBox "New date after adding 1 week : " & oDate, vbInformation, "VBA DateAdd Function"
    
End Sub

Output: Here is the screen shot of the fourth example output.
VBA DateAdd Function

Example 5: Add 3 hours to the time

One more example of the VBA DateAdd function. This below example adds 3 hours to the time.

'Add 3 hours to the time
Sub VBA_DateAdd_Function_Ex5()

    'Variable declaration
    Dim lTime As Date
    Dim oTime As Date
            
    lTime = "02:04:18"
    oTime = DateAdd("h", 3, lTime)
        
    MsgBox "New time after adding 3 hours : " & oTime, vbInformation, "VBA DateAdd Function"
    
End Sub

Output: Here is the screen shot of the fifth example output.
VBA DateAdd Function

Instructions to use Macro Codes

Here are the step by step instructions to use the VBA DateAdd function in Excel VBA.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Copy above specified procedures or macros to the VBA editor.
  • Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.

VBA Date & Time Functions List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *