How to Use the VBA DateAdd Function in Excel

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


Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the Excel VBA DateAdd Function

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

Examples of the Excel VBA DateAdd Function

Formula Expressions of the Excel 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 Developer Tab and select 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

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


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


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


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


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


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


Use of the DateAdd Function in Excel 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 on 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 Calender 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


Further Readings

 

Al Arafat Siddique

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

Leave a reply

ExcelDemy
Logo