Mentally calculating a date a few periods from now is not hard: finding the date five days in the future, three months from now, or two years into the past is relatively straightforward arithmetic.

When we start to cross over intervals, however, it becomes more difficult. Is 180 days from now exactly 6 months away, like March 28 → September 28? Or is it a little shifted due to differences in month length? What about 26 weeks? VBA has a built-in function to make calculations like these easier for both past and future dates.

Let’s look at how the VBA DateAdd function can help you calculate dates and how we can manipulate the outputs to simplify the results for users.


The Built-in DateAdd Function

Let’s start by breaking down the function, which has three parameters, all of which are required:

DateAdd(Interval, Number, Date) where,

  • Interval as String
  • Number as Double
  • Date as Variant(Date)

The DateAdd function returns a single Variant(Date) as the result.

The Interval Parameter

According to Microsoft’s own documentation, there are 10 intervals you can use for DateAdd. This parameter should be passed as a String to the function, with the following interpretations:

Setting Description
yyyyYear
qQuarter
mMonth
yDay of year
dDay
wWeekday
wwWeek
hHour
nMinute
sSecond

For example, if you want to add seconds to a date, the Interval parameter should be "s". If you want to subtract years, pass "yyyy" as the Interval.


Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below.

Sure, I'll take a free VBA Developer Kit

Okay, now you need to really pay attention. There are three types of days in the table above that do not have any bearing on the days added or subtracted: d, w, and y. Specifically, the w weekday parameter does not have a cycle of 5 days, as one would expect! Entering these 3 values in your Interval argument will not do anything to your original date.

This quirk seems to arise because Microsoft uses the same interval set for different functions, like DateDiff and DatePart, where d, w, and y do carry different meanings.

The Number Parameter

The Number argument specifies how many of Interval you want to add or subtract. If your Interval is "yyyy" and your Number is 20, the output of DateAdd will be twenty years from the starting date (supplied in Date).

To add time, use positive numbers. To subtract time, use negative numbers. One point to note is that, though Number’s data type is Double, which carries up to 15 decimal points, you cannot add or subtract fractional intervals.

To illustrate, this code will always output the starting time, "10/23/2019 14:35:31", regardless of how big i is, because partial intervals (intervals with decimals) are not added or stored. Despite being a double data type, the Number argument only interprets integers.

Sub partial_interval_added_iteratively()
'VBA DateAdd cannot add fractional time!
y = "10/23/2019 14:35:31"

For i = 0 To 100
    y = DateAdd("yyyy", 0.5, y)
Next i

Debug.Print y

End Sub

Consequently there is no resolution below one second. Moreover, dates that result in years outside the range [100, 9999] (inclusive) will result in an error. The Date data type in VBA does not allow dates outside this range.

No one ever claimed VBA is a scientific computing language. If you need better than one second resolution or dates outside this period, which may be common in many disciplines like Earth Sciences, Astronomy, and even Archaeology, you might need a different language for date calculations, like Python. I mean, VBA can do it if you’re willing to risk string manipulations and forced data type conversions, but that’s a dangerous game to play.

The Date Parameter

The variable type for Date is Variant, and you may use either a string date, such as "Oct 21 1992 12:34:23", "21-Oct-92", "10-2019", or any other common string format for dates. Since DateAdd also handles times, you can use a human-readable time expression, like "12:30:15" or the VBA internal representation, which is a Double indicating the fraction of the day elapsed.

In VBA internal time representation, a few examples are:

  • 12:00:00 = 0.5
  • 21:00:00 = 0.875 (21/24th of the day)
  • 00:00:01 = 1/(60 seconds * 60 minutes * 24 hours in a day) = 0.000011574

If you do not specify a day for a Date month-year entry, VBA will default to the first day. If you do not specify a time, VBA will default to midnight of that day. Thus

Debug.Print DateAdd("s", 1, "Oct 2019")

will print 10/01/2019 00:00:01, one second after midnight on October 1st.

When specifying the date, it is advisable to provide as much information as possible to avoid forcing VBA to guess what you mean. Consider the following short dates:

  • 10-2019
  • 10-19

When seen next to each other, a human may automatically assume the pattern of October 2019 for both. However, the latter entry will be interpreted by VBA as October 19 of the current year.

Above, 10-2019 references a month, and thus defaults to midnight on October 01, 2019, while 10-19 references a day and thus defaults to midnight on October 19, [current year]. The less ambiguity VBA must deal with, the less likely you are to run into an unintended outcome.

For clarity, you can use the VBA cDate function to convert your strings to dates.

Once you’ve added your dates using the DateAdd function, remember you can format dates using VBA so your output can be printed however you like.

Loops with Pauses

It’s possible to delay a loop’s progress in different ways. One way is to use DateAdd. The most common use here is for waiting a few seconds between iterations. People often do this to allow other processes to finish before continuing their macro. For a better approach to automation, especially over longer periods of time, see our tutorial on scheduling macros.

The easiest solution is to add seconds to the current time, which is conveniently accessible with the built-in Now function. A skeletal example is shown below.

Sub delayed_loop_name_guessing_game()
Do Until user_input = "stop" Or user_input = "John" Or user_input = "Sarah"
    user_input = InputBox("Enter a name to play or 'stop' to stop the game", "Guess a Name Game")
    Application.Wait (DateAdd("s", 3, Now()))
Loop

If user_input = "John" Or user_inut = "Sarah" Then MsgBox ("You win!")
End Sub

This (admittedly excessively) basic game asks the user to guess a name then compares it to the winning names (John and Sarah). To give the user some time to think, it pauses the loop for 3 seconds. Of course, the setup also means evaluation is delayed for 3 seconds, so even if they win, they must wait 3 seconds for the game to conclude.

Regardless of the triviality of the game, this code illustrates using DateAdd to pause execution for a time interval. If you’re interested in adding delays to your macro, check out our detailed tutorial about the Applicaion.Wait VBA method.


Conclusion

The built-in DateAdd function is quite useful for finding dates in the future or past. There are a couple unfortunate characteristics, like the inability to directly calculate business days or partial intervals.

If you liked this tutorial, we have a 20+ part free email series designed to help you learn more VBA tricks like this one. Check it out using the form below and share this article on Twitter and Facebook.


The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Let me join the wellsrPRO VBA Training program for free