DateAdd VBA function – How to add date and time

Last Updated on January 11, 2023 by token

1. DateAdd VBA function – Description

DateAdd VBA function is the date or time plus or minus a specified number of time units. So if we want to enlarge the date by e.g. a certain number of days, months and years, we will use this function. We use this function when we want to do the following:

• Add days to date. Subtract days from date.
• Add months to date. Subtract the months from the date.
• Add years to date. Subtract the years from the date.
• Add weeks to date. Subtract weeks from date.
• Add quarters to date. Subtract the quarters from the date.
• Add hours to time or date. Subtract hours from time or date.
• Add minutes to time or date. Subtract minutes from time or date.
• Add seconds to time or date. Subtract seconds from time or date.

2. VBA DateAdd function – Syntax

DateAdd (Interval As String, Number As Double, Date)

Interval : Specify the unit of time by which you want to enlarge or reduce the date or time. It can be a day, a month, a year, an hour, a minute, a second. Details are presented in the table below. Enter unit names in quotation marks, eg “YYYY”, if you want to add or subtract years from the date.

Number : The number of time units we want to enlarge or reduce the date or time by.

Date : The date or time we want to zoom in or out by units of time.

Function return: Date / A date, time or date and time value.

3. VBA DateAdd function – Example

How to use DateAdd function in VBA Excel? Below is an example of using the DateAdd function in the VisualBasic Editor.

Example 1: We subtract 2 months from the date and display the result in the form of an MsgBox window.

Dim datValue As Date
datValue = # 1/15/2018 #
MsgBox DateAdd ("m", -2, datValue)

Example 2: We add to the date of week 10 and display the result in the MsgBox window.

Dim datValue2 As Date
datValue2 = # 1/15/2018 #
MsgBox DateAdd ("ww", 10, datValue2)

Example 3: Add / Subtract from date and time hours and seconds. We display the result in the form of an MsgBox window.

Dim datValue3 As Date
datValue3 = # 1/15/2018 3:00:00 AM #
MsgBox DateAdd ("h", -10, datValue3)
MsgBox DateAdd ("n", 240, datValue3)

Example 4: We add the date to the 10th day and enter the result into the worksheet.

Dim datValue4 As Date
datValue4 = # 6/25/2018 #
Range ("A1") = DateAdd ("d", 120, datValue4)

Example 5: We enter the date from the sheet. Then we get the date from the sheet. We add 2 years to the date and display in the MsgBox window.

Dim datValue5 As Date
datValue5 = # 6/25/2018 #
Range ("A1") = datValue5
MsgBox DateAdd ("YYYY", 2, Range ("a1"))

Excel file with examples:  functions_vba_excel_dateadd.xlsm

4. VB DateAdd function – Additional information

• If we want to subtract selected values ​​from the date, we should enter the second argument of the function with a minus sign.
• Using the DateAdd VBA function, we can add to / subtract from a date days, months, weeks, quarters, years, hours, minutes, seconds.
• If in VBA we want to add a certain number of days to a date or subtract a certain number of days, we can use the usual arithmetic operations for this purpose, as in the case of numbers

5. DateAdd VisualBasic function – Where to use?

The function can be used in: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365.

The article is part of the VBA Excel function list. You can find a list of all VBA functions at this address: VBA functions .