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.
|Day of the year||y|
|Day of the week||in|
|Week of the year||above|
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.
The article is part of the VBA Excel function list. You can find a list of all VBA functions at this address: VBA functions .