DateAdd VBA function – How to add date and time

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.

UnitInterval
Dayd
Day of the yeary
Hourh
Minuten
Monthm
Quarterq
Seconds
Day of the weekin
Week of the yearabove
Yearyyyy

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 VBA functions - DateAdd VBA function
Excel VBA functions – DateAdd VBA function

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 .

Leave a comment

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

%d bloggers like this: