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
- Loops with Pauses
Let’s start by breaking down the function, which has three parameters, all of which are required:
DateAdd(Interval, Number, Date) where,
The DateAdd function returns a single Variant(Date) as the result.
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:
|y||Day of year|
For example, if you want to add seconds to a date, the
Interval parameter should be
Make powerful spreadsheets with our free VBA Developer's Guide This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy to your email address below.
This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy 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
Number argument specifies how many of
Interval you want to add or subtract. If your
DateAdd will be twenty years from the starting date (supplied in
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 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 variable type for
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")
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:
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.
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.