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,
Intervalas StringNumberas DoubleDateas 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 |
|---|---|
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
For example, if you want to add seconds to a date, the Interval parameter should be 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, along with our Big Book of Excel VBA Macros, to your email address below.
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: 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
The Number Parameter
The Number argument specifies how many of Interval you want to add or subtract. If your Interval is Number is 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, 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 SubConsequently 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 DateAdd also handles times, you can use a human-readable time expression, like 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
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 SubThis (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.
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.
