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

Excel VBA DateAdd Function Overiview

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

Excel VBA DateAdd function


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

Follow the steps:

  • From the Excel Ribbon, go to the Developer Tab and select the Visual Basic Tab.

Run Code on Visual Basic Editor

  • From the new window, click the Insert tab and choose Module.

Run Code on Visual Basic Editor

  • 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).

Excel VBA DateAdd Function Add Year

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).

Excel VBA DateAdd Function Add Quarter

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).

Excel VBA DateAdd Function Add Month

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).

Excel VBA DateAdd Function Add Day of Year

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


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).

Excel VBA DateAdd Function Add WeekDay


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).

Excel VBA DateAdd Function

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).

Excel VBA DateAdd Function Add Hours


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).

Excel VBA DateAdd Function Add Second


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).

Excel VBA DateAdd Function


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


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo