Date and Time Functions

Many times, you will need to format a date or time to have a specific appearance. As an example, March 30, 2004 could look like that or:

3/30/2004

or

Tuesday March 30, 2004 

Of course, there is a whole variety of possibilities. However, in addition to doing formatting, you may also want to calculate the number of days between, say, January 3, 2003 and March 30, 2004.

VBA has a number of built-in Date and Time functions to assist with formatting and date arithmetic. The following program shows the basic functions. Most of the functions you will see are, in my opinion, self-explanatory. I added some explanations where I felt it was needed. I strongly suggest you try the following code examples.

Sub dateFunctions()   Dim strDateString As String   strDateString = "The present date and time is: " & Now & vbCrLf & _   "Today's date is: " & Date & vbCrLf & _   "The day of the month is: " & Day(Date) & vbCrLf & _   "The day of the week is: " & Weekday(Date) & vbCrLf & _   "The name of the week day is: " & WeekdayName(Weekday(Date)) & vbCrLf & _   "The weekday abbreviated is: " & WeekdayName(Weekday(Date), True) & vbCrLf & _   "The month is: " & Month(Date) & vbCrLf & _   "The name of the month is: " & MonthName(Month(Date)) & vbCrLf & _   "The month abbreviated is: " & MonthName(Month(Date), True) & vbCrLf & _   "The year is: " & Year(Date)   MsgBox strDateString End Sub 

This will result in the message box shown in Figure 11-1.

Figure 11-1: A compendium of date functions

Notice that two lines of the previous code have the extra argument of True added. This tells VBA to abbreviate the names of the month and weekday.

You can also break a single date down to its individual components using the DatePart function shown in the following example:

Sub dateFunctions()   Dim strDateString As String   strDateString = "The year part is: " & DatePart("yyyy", Date) & vbCrLf & _   "The quarter part is: " & DatePart("q", Now) & vbCrLf & _   "The month part is: " & DatePart("m", Now) & vbCrLf & _   "The day part is: " & DatePart("d", Now) & vbCrLf & _   "The weekday is: " & DatePart("w", Now) & vbCrLf & _   "The week part is: " & DatePart("ww", Now) & vbCrLf & _   "The hour part is: " & DatePart("h", Now) & vbCrLf & _   "The minute part is: " & DatePart("n", Now) & vbCrLf & _   "The second part is: " & DatePart("s", Now)   MsgBox strDateString End Sub 

This will give the response shown in Figure 11-2.

Figure 11-2: The DatePart function

You could use the DateDiff function to calculate the differential between two dates. This function takes three arguments: the unit of measure, the first date, and the second date.

The following example shows the difference between two dates in days and months:

Sub dateFunctions()   Dim strDateString As String   strDateString = "The days between 3/15/2000 and today is: " & _   DateDiff("d", "3/15/2000", Now) & vbCrLf & _   "The months between 3/15/2000 and today is: " & _   DateDiff("m", "3/15/2000", Now)   MsgBox strDateString End Sub

This results in output similar to that shown in Figure 11-3.

Figure 11-3: The DateDiff function output

Note that if you put the later date as the first argument, a negative number will show up in the output.

Notice that Now returns today’s date and time.

There are several different ways to approach formatting the date and time. The following code shows the possibilities.

Sub dateFunctions()   Dim strDateString As String   strDateString = "m/d/yy: " & Format(Now, "m/d/yy") & vbCrLf & _   "d-mmm-yy: " & Format(Now, "d-mmm-yy") & vbCrLf & _   "d-mmmm-yy: " & Format(Now, "d-mmmm-yy") & vbCrLf & _   "mmmm d, yyyy: " & Format(Now, "mmmm d, yyyy") & vbCrLf & _   "ddd: " & Format(Now, "ddd") & vbCrLf & _   "dddd: " & Format(Now, "dddd") & vbCrLf & _   "ddddd: " & Format(Now, "ddddd") & vbCrLf & _   "dddddd: " & Format(Now, "dddddd") & vbCrLf & _   "Hh:Nn:Ss AM/PM: " & Format(Now, "Hh:Nn:Ss AM/PM") & vbCrLf & _   "ttttt: " & Format(Now, "ttttt") & vbCrLf & _   "vbShortDate: " & FormatDateTime(Now, vbShortDate) & vbCrLf & _   "vbLongDate: " & FormatDateTime(Now, vbLongDate) & vbCrLf & _   "vbGeneralDate: " & FormatDateTime(Now, vbGeneralDate)   MsgBox strDateString End Sub

This would result in the output shown in Figure 11-4.

Figure 11-4: Possible date formats

As you can see, there are different ways to format the date by using either the Format function or the FormatDateTime function.