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.

Unit	Interval
Day	d
Day of the year	y
Hour	h
Minute	n
Month	m
Quarter	q
Second	s
Day of the week	in
Week of the year	above
Year	yyyy

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