# How to Use the VBA DateAdd Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

The VBA DateAdd function is under the date and time category of Excel’s VBA functions. By using this function we can add or subtract years, months, days, quarters, and even different time intervals like hours, minutes, and seconds from a given date. Dealing with date and time in daily calculations for generating reports or making comparisons is a common scenario. In Excel, the use of VBA date and time functions like the DateAdd function makes complex or time-consuming calculations more efficient and faster.

## Excel VBA DateAdd Function Overview Outcome:
a date to which a specific time interval is added or subtracted

Syntax:

Arguments:

Argument Required/Optional Description
interval Required A string expression.
The interval of time in different settings that we want to add
number Required A numeric expression.
The number of intervals to be added or subtracted
Can be positive– for future dates
Can be negative– for past dates
date Required A date expression
The date to which the intervals are added

Settings:
The DateAdd function has these interval settings:

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

## Formula Expressions of the VBA DateAdd Function

There are different ways to put the date argument into the DateAdd function. They all result in the same output.
Put the following code in the Visual Basic Editor:
(How to run code in the Visual Basic Editor)

``````Sub add_year()
Range("D4") = DateAdd("yyyy", 2, DateSerial(2022, 1, 1))
Range("D5") = DateAdd("yyyy", 2, DateValue("Jan 1, 2022"))
End Sub`````` Explanation:
DateAdd(“yyyy”,2, one of the following methods)
To put the date argument we can use different methods:
• #1/1/2011#
• DateSerial(year, month, day)
• DateValue(date)
• Range(“cell”) – Date stored in a cell
• Storing the Date in a variable

In cells D3, D4, D5, D6, D7 we put the above methods as the date argument of the DateAdd function sequentially and got the same result.

We added 2 more years to 1/1/2022 which resulted in 1/1/2024. </strong
Here,
yyyy represents year as interval
2 represents the no of intervals as number.

### Help: How to Run Code in the Visual Basic Editor

• From the Excel Ribbon, go to the Developer Tab and select the Visual Basic Tab. • From the new window, click the Insert tab and choose Module. • Write your code in the editor and press F5 to run. ## Adding Different Interval Settings Using the DateAdd Function in Excel VBA

Code:

``````Sub DateAdd_Years()
End Sub``````

Result: 2 Years added to 1/1/2022(mm/dd/yyyy) and resulted in 1/1/2024(mm//dd/yyyy). Read More: How to Use Year Function in Excel VBA (5 Suitable Examples)

Code:

`````` Sub DateAdd_Quarters()
Range ("F5") = DateAdd("q", 2, Range("D5"))
End Sub``````

Result: 2 Quarter = 6 months added to 1/1/2022(mm/dd/yyyy) and resulted in 7/1/2022(mm//dd/yyyy). Code:

``````Sub DateAdd_Months()
End Sub``````

Result: 2 Months added to 1/1/2022(mm/dd/yyyy) and resulted in 3/1/2022(mm//dd/yyyy). Read More: How to Use Excel VBA MONTH Function (7 Suitable Examples)

### 4. Add Day of Year

Code:

``````Sub DateAdd_DayOfYear()
End Sub``````

Result: 2 Day of Year added to 1/1/2022(mm/dd/yyyy) and resulted in 1/3/2022(mm//dd/yyyy). Read More: How to Use the Day Function in Excel VBA (3 Examples)

Code:

``````Sub DateAdd_Day()
End Sub``````

Result: 2 Days added to 1/1/2022(mm/dd/yyyy) and resulted in 1/3/2022(mm//dd/yyyy). Code:

``````Sub DateAdd_WeekDay()
End Sub``````

Result: 10 WeekDays added to 1/1/2022(mm/dd/yyyy) and resulted in 1/11/2022(mm//dd/yyyy). Code:

``````Sub DateAdd_Weeks()
End Sub``````

Result: 2 Weeks= 14 Days added to 1/1/2022(mm/dd/yyyy) and resulted in 1/15/2022(mm//dd/yyyy). Read More: How to Get the Day of Week Using VBA (4 Suitable Methods)

Code:

``````Sub DateAdd_Hours()
End Sub``````

Result: 14 Hours added to 1/1/2022 12:00 AM (mm/dd/yyyy: hh/mm) and resulted in 1/1/2022 2:00 PM (mm//dd/yyyy : hh/mm). Code:

``````Sub DateAdd_Minutes()
End Sub``````

Result: 90 Minutes= 1.30 Hours added to 1/1/2022 12:00 AM (mm/dd/yyyy) and resulted in 1/1/2022 1:30 AM (mm//dd/yyyy). Code:

``````Sub DateAdd_Seconds()
End Sub``````

Result: 120 Seconds = 2 Minutes added to 1/1/2022 12:00 AM (mm/dd/yyyy : hh/mm) and resulted in 1/1/2022 12:02 AM(mm//dd/yyyy : hh/mm). ## Using Excel DateAdd Function to Subtract Different Interval Settings

Similarly, we can subtract years, months, days, hours, minutes, etc from a date by using a minus sign in front of the number argument. For example:

Code:

``````Sub DateAdd_Subtract_Years()
End Sub``````

Result: 2 Years subtracted from 1/1/2022(mm/dd/yyyy) and resulted in 1/1/2020(mm//dd/yyyy). ## Things to Remember

• When we use ‘w’ to add weekdays it adds up all the days of a week including Saturday and Sunday, not the workdays only(someone might expect).
• The DateAdd function doesn’t end up showing an invalid date. For example, if we add 1 month to Jan 31, 2022, it’ll result in Feb 28, 2022, not Feb 31, 2022(it doesn’t exist).
• If we subtract more than 122 years from now an error would occur because the Excel date starts from Jan 1, 1990.
• The return date of the DateAdd function depends on the Control Panel Date Settings.
• We should the date argument of the DateAdd function according to the Calendar Property. If the calendar is Gregorian, the input date argument should also be in Gregorian. Similarly, if the calendar is in Hijri, the date argument must be in the same format.

## Conclusion

Now, we know how to use the VBA DateAdd function in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below

## Related Articles Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  