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:
DateAdd(interval, number, date)
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("D3") = DateAdd("yyyy", 2, #1/1/2022#)
Range("D4") = DateAdd("yyyy", 2, DateSerial(2022, 1, 1))
Range("D5") = DateAdd("yyyy", 2, DateValue("Jan 1, 2022"))
Range("D6") = DateAdd("yyyy", 2, Range("B6"))
End Sub
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
Follow the steps:
- 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
1. Add Year
Code:
Sub DateAdd_Years()
Range("F5") = DateAdd("yyyy", 2, Range("D5"))
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)
2. Add Quarter
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).
Read More: How to Use VBA DateSerial Function in Excel (5 Easy Applications)
3. Add Month
Code:
Sub DateAdd_Months()
Range("F5") = DateAdd("m", 2, Range("D5"))
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()
Range("F5") = DateAdd("y", 2, Range("D5"))
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)
5. Add Day
Code:
Sub DateAdd_Day()
Range("F5") = DateAdd("d", 2, Range("D5"))
End Sub
Result: 2 Days added to 1/1/2022(mm/dd/yyyy) and resulted in 1/3/2022(mm//dd/yyyy).
Similar Readings
- Excel VBA to Find Week Number (6 Quick Examples)
- How to Use the VBA DatePart Function in Excel (7 Examples)
- Convert Date from String Using VBA (7 Ways)
- How to Use VBA DateValue Function in Excel (6 Examples)
- Use EoMonth in Excel VBA (5 Examples)
6. Add Weekday
Code:
Sub DateAdd_WeekDay()
Range("F5") = DateAdd("w", 10, Range("D5"))
End Sub
Result: 10 WeekDays added to 1/1/2022(mm/dd/yyyy) and resulted in 1/11/2022(mm//dd/yyyy).
7. Add Week
Code:
Sub DateAdd_Weeks()
Range("F5") = DateAdd("ww", 2, Range("D5"))
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)
8. Add Hour
Code:
Sub DateAdd_Hours()
Range("F5") = DateAdd("h", 14, Range("D5"))
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).
9. Add Minute
Code:
Sub DateAdd_Minutes()
Range("F5") = DateAdd("n", 90, Range("D5"))
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).
10. Add Second
Code:
Sub DateAdd_Seconds()
Range("F5") = DateAdd("s", 120, Range("D5"))
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()
Range("F5") = DateAdd("y", -2, Range("D5"))
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.
Download the Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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