How to handle dates with VBA

This post is about doing all kind of operations on Dates, like adding intervals, substracting dates or finding the Xth working day in the future. Every now and then, I need to use one of these functions to do a specific tasks only to notice I had forgotten what’s the function name or how to use its parameters. So here it is – the missing manual on Date functions!

Declare and intialise Date functions

You should always declare your variables to prevent Type errors and automatic type conversions. Here is how:

 

If today was the 1st of January 2015, dat_today would now hold this date. You have to consider, that date variables in VBA, can hold date, time or even date and time!

 

In Code Snippet 2 we change the value of dat_today three times, printing its content to the immediate window shortly afterwards. A sample output, you might receive in the immediate window, is shown in Listing 1:

Adding a time span to your date

After initializing your date with Date(), Time() or Now(), an algorithm or from a database table, you might want to add a specific time span to this date. VBA offers you this functionality with the function DateAdd(Interval as String, Number as Long, StartDate as Date) As Date, which requires the arguments Number (the quantity of Interval, e.g. days or years to add to your StartDate) and returns the resulting date. While Number must be a Long-Value, Interval is a string code for the intended time unit (e.g. “d” for days, “yyyy” for years or “n” for minutes; see the MSDN link below for a complete reference).

Code Snippet 3 shows an addition of months and a substraction of seconds to the Date/Time value of the Now function:

 

Which might give the output seen in Output Listing 2, depending on your current time:

In the next section I will show you how to retrieve the time span between two dates in your unit of choice.

Retrieving the time span between two dates

To find out the number of days you have to wait until your birthday or Christmas or until the release date of your favorite new product, you use DateDiff. DateDiff(Interval as String, StartDate as Date, EndDate as Date) as Long, similar to DateAdd, requires three arguments: The interval as String (e.g. “d” for day, “m” for month, same as for the DateAdd function). StartDate is the Date earlier than EndDate, in which case you will get a positive Long-Value as Return Value. If you set the later date first, the time span will have a negative sign.

Code Snippet 4 shows example usage of the the DateDiff function:

 

Code Snippet 4 will give you the output shown in Output Listing 3:

Format dates or part of them into strings

Often you may come to the point where you want to format your calculated or modified date back into a string to print it to the user or write it into a table in your database. Here the function Format comes in handy, that helps you to force your date/time information in a string exactly in the form you want it to be. The Format function may also be used to format numbers, by the way. For now, only two of optionally four arguments of the function Format(expression as Variant, format as String) as String are relevant for us. To provide a date as expression, we can choose to deliver a string variable or a date function. In case of a string VBA will automatically recognize the date character, if your format implies the expression to be a date and the format of the string is any kind of date format.

Now our format string: similar to the interval variable for DateDiff and DateAdd functions you may use “d” or “dd” for the day of the month (later will use leading zeros), “mm” or “m” will respectively return the month with/without leading zeros. Three mmms in a row (“mmm”) will return the full month name’s abbreviation in three letters and four mmmms the full month’s name, depending on your local language (e.g. January for English system language). Furthermore, “yy” and “yyyy” will return the last or all four digits of the date’s year. Equal to “d” and “dd”, “h” and “hh”, “n” and “nn”, “s” and “ss” will return hours, minutes and seconds without/with leading zeros respectively.

Now the clue is, that you can combine all of the symbols named in the above paragraph, in a custom string with all additional letters and symbols that you want. E.g. to format your date dat_yourdate in U.S. date format, you would use: Format(dat_yourdate,”mm/dd/yyyy”). Here / corresponds to your local date separator. If that one should not be the symbol “/”, you must escape the slash again to get it in the string: Format(dat_yourdate,”mm\/dd\/yyyy”). To return any functional letter sequence itself inside your string (e.g. “dd”) you need to escape it, too: Format(“12:45″,”h\h”) will return the string “12h”.

Let us get to the examples. Code snippet 5 shows combined usage of DateAdd, DateDiff and Format functions:

 

Find your dates nicely formatted in Output Listing 4:

Find more info on MSDN

This is all you need to know about entering, processing and formatting dates so far. If you have any more questions, let me know in the comments below. You can also find more information in Microsoft’s online documentation:

MSDN, the Microsoft Developer Network, has detailed documentation for all the VBA functions that exist. While having information on many functions, the presented resources do not always answer all your questions. Nonetheless, here you can find links to the documentation of all the elements and functions we used in this post:

• Date Data Type
• Functions to determine current Date/Time
• DateAdd
• DateDiff
• Format