BET: Microsoft VBA Dates & Times - Adding and Subtracting

Leading the way in Microsoft Office Development

Home

Excel

Word

PowerPoint

VBA

VBA > Dates & Times > Adding and Subtracting

< Previous | Next >

VBA provides a data type for storing date and time values, the Date data type. Convenient as the Date data type is, manipulating date values in VBA can still be tricky. To easily work with dates, you need to understand how VBA stores date values internally. Note The Microsoft Office 2000 applications all support dates beyond the year 2000. However, there are some issues you need to take into account in order to design your application so that it properly handles both twentieth and twenty-first century dates

Date Data Type

	VBA provides the Date data type to store date and time values. The Date data type is an 8-byte floating-point value, so internally it's the same as the Double data type. The Date data type can store dates between January 1, 100, and January 1, 9999.
	VBA stores the date value in the integer portion of the Date data type, and the time value in the decimal portion. The integer portion represents the number of days since December 30, 1899, which is the starting point for the Date data type. Any dates before this one are stored as negative numbers; all dates after are stored as positive values. If you convert a date value representing December 30, 1899, to a double, you'll find that this date is represented by zero.
	The decimal portion of a date represents the amount of time that has passed since midnight. For example, if the decimal portion of a date value is .75, three-quarters of the day has passed, and the time is now 6 P.M.
	Because the integer portion of a date represents number of days, you can add and subtract days from one date to get another date.

Getting the Current Date and Time

	Three functions in VBA can tell you exactly when it is: the Now, Date, and Time functions. The Now function returns both the date and time portions of a Date variable. For example, calling the Now function from the Immediate window returns a value like this one:
	2/23/98 6:16:47 PM

Formatting a Date

	You can use predefined formats to format a date by calling the FormatDateTime function, or you can create a custom format for a date by using the Format function.
	The following procedure formats a date by using both built-in and custom formats:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Sub DateFormats(Optional dteDate As Date)
   ' This procedure formats a date using both built-in
   ' and custom formats.

   ' If dteDate argument has not been passed, then
   ' dteDate is initialized to 0 (or December 30, 1899,
   ' the date equivalent of 0).
   If CLng(dteDate) = 0 Then
      ' Use today's date.
      dteDate = Now
   End If

   ' Print date in built-in and custom formats.
   Debug.Print FormatDateTime(dteDate, vbGeneralDate)
   Debug.Print FormatDateTime(dteDate, vbLongDate)
   Debug.Print FormatDateTime(dteDate, vbShortDate)
   Debug.Print FormatDateTime(dteDate, vbLongTime)
   Debug.Print FormatDateTime(dteDate, vbShortTime)
   Debug.Print Format$(dteDate, "ddd, mmm d, yyyy")
   Debug.Print Format$(dteDate, "mmm d, H:MM am/pm")
End Sub

Date Delimiters

	When you work with date literals in your code, you need to indicate to VBA that a value is a date. If you don't, VBA may think you're performing subtraction or floating-point division.
	For example, if you run the following fragment, the value that VBA assigns to the Date variable is not April 5, 1998, but 4 divided by 5 divided by 98. Because you're assigning it to a Date variable, VBA converts the number to a date, and prints "12:11:45 AM" to the Immediate window:

22
23
24

Dim dteDate As Date
dteDate = 4 / 5 / 98
Debug.Print dteDate

To avoid this problem, you must include delimiters around the date. The preferred date delimiter for VBA is the number sign (#). You can also use double quotation marks, as you would for a string, but doing so requires VBA to perform an extra step to convert the string to a date. If you rewrite the fragment as follows to include the date delimiter, VBA prints "4/5/98" to the Immediate window:

25
26
27

Dim dteDate As Date
dteDate = #4/5/98#
Debug.Print dteDate

Assembling a Date

	To work with a date in code, you sometimes need to break it down into its component parts—that is, its day, month, and year. Once you've done this, you can perform a calculation on one element, and then reassemble the date. To break a date into components, you can use the Day, Month, and Year functions. Each of these functions takes a date and returns the day, month, or year portion, respectively, as an Integer value. For example, Year(#2/23/98#) returns "1998."
	To reassemble a date, you can use the DateSerial function. This function takes three integer arguments: a year, a month, and a day value. It returns a Date value that contains the reassembled date.
	Often you can break apart a date, perform a calculation on it, and reassemble it all in one step. For example, to find the first day of the month, given any date, you can write a function similar to the following one:

28
29
30
31
32
33
34
35
36
37
38
39

Function FirstOfMonth(Optional dteDate As Date) As Date

   ' This function calculates the first day of a month, given a date.
   ' If no date is passed in, the function uses the current date.

   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If

   ' Find the first day of this month.
   FirstOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)
End Function

	The FirstOfMonth procedure takes a date or, if the calling procedure doesn't pass one, uses the current date. It breaks the date into its component year and month, and then reassembles the date using 1 for the day argument. Calling this procedure with the dteDate argument #2/23/98# returns "2/1/98".
	The following procedure uses the same strategy to return the last day of a month, given a date:

40
41
42
43
44
45
46
47
48
49
50
51

Function LastOfMonth(Optional dteDate As Date) As Date

   ' This function calculates the last day of a month, given a date.
   ' If no date is passed in, the function uses the current date.

   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If

   ' Find the first day of the next month, then subtract one day.
   LastOfMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - 1
End Function

	Both of these procedures are available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.
	VBA also provides functions that you can use to disassemble and reassemble a time value in the same manner. The Hour, Minute, and Second functions return portions of a time value; the TimeSerial function takes an hour, minute, and second value and returns a complete time value.

Getting Part of a Date

	The previous section showed how to return the year, month, and day from a date. You can get other information about a date as well, such as what quarter or week it falls in, or what day of the week it is.
	The Weekday function takes a date and returns a constant indicating on what day of the week it falls. The following procedure takes a date and returns True if the date falls on a workday—that is, Monday through Friday—and False if it falls on a weekend.

52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

Function IsWorkday(Optional dteDate As Date) As Boolean
   ' This function determines whether a date
   ' falls on a weekday.

   ' If no date passed in, use today's date.
   If CLng(dteDate) = 0 Then
      dteDate = Date
   End If

   ' Determine where in week the date falls.
   Select Case Weekday(dteDate)
      Case vbMonday To vbFriday
         IsWorkday = True
      Case Else
         IsWorkday = False
   End Select
End Function

	This procedure is available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.
	In addition to the individual functions that return part of a date—Year, Month, Day, and Weekday—VBA includes the DatePart function, which can return any part of a date. Although it may seem redundant, the DatePart function gives you slightly more control over the values you return, because it gives you the option to specify the first day of the week and the first day of the year. For this reason, it can be useful when you're writing code that may run on systems in other countries. In addition, the DatePart function is the only way to return information about what quarter a date falls into.

Adding and Subtracting Dates

	To add an interval to a given date, you must use the DateAdd function, unless you're adding days to a date. As mentioned earlier, since the integer portion of a Date variable represents the number of days that have passed since December 30, 1899, adding integers to a Date variable is equivalent to adding days.
	By using the DateAdd function, you can add any interval to a given date: years, months, days, weeks, quarters. The following procedure finds the anniversary of a given date; that is, the next date on which it occurs. If the anniversary has already occurred this year, the procedure returns the date of the anniversary in the next year.

69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

Function Anniversary(dteDate As Date) As Date
   ' This function finds the next anniversary of a date.
   ' If the date has already passed for this year, it returns
   ' the date on which the anniversary occurs in the following year.

   Dim dteThisYear As Date

   ' Find corresponding date this year.
   dteThisYear = DateSerial(Year(Date), Month(dteDate), Day(dteDate))
   ' Determine whether it's already passed.
   If dteThisYear < Date Then
      Anniversary = DateAdd("yyyy", 1, dteThisYear)
   Else
      Anniversary = dteThisYear
   End If
End Function

	This procedure is available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.
	To find the interval between two dates, you can use the DateDiff function. The interval returned can be any of several units of time: days, weeks, months, years, hours, and so on.
	The following example uses the DateDiff function to return the day number for a particular day of the year. The procedure determines the last day of the last year by using the DateSerial function, and then subtracts that date from the date that was passed in to the procedure.

85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

Function DayOfYear(Optional dteDate As Date) As Long

   ' This function takes a date as an argument and returns
   ' the day number for that year. If the dteDate argument is
   ' omitted, the function uses the current date.

   ' If dteDate argument has not been passed, dteDate is
   ' initialized to 0 (or December 30, 1899, the date
   ' equivalent of 0).
   If CLng(dteDate) = 0 Then
      ' Use today's date.
      dteDate = Date
   End If

   ' Calculate the number of days that have passed since
   ' December 31 of the previous year.
   DayOfYear = Abs(DateDiff("d", dteDate, _
      DateSerial(Year(dteDate) - 1, 12, 31)))
End Function

Calling this procedure with the value of #2/23/98# returns "54."

Calculating Elapsed Time

	You can use the DateAdd and DateDiff functions to calculate the time that has elapsed between two dates, and then, with a little additional work, present that time in the desired format. For example, the following procedure calculates a person's age in years, taking into account whether his or her birthday has already occurred in the current year.
	Using the DateDiff function to determine the number of years between today and a birthdate doesn't always give a valid result because the DateDiff function rounds to the next year. If a person's birthday hasn't yet occurred, using the DateDiff function will make the person one year older than he or she actually is.
	To remedy this situation, the procedure checks to see whether the birthday has already occurred this year, and if it hasn't, it subtracts 1 to return the correct age.

104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

Function CalcAge(dteBirthdate As Date) As Long

   Dim lngAge As Long

   ' Make sure passed-in value is a date.
   If Not IsDate(dteBirthdate) Then
      dteBirthdate = Date
   End If

   ' Make sure birthdate is not in the future.
   ' If it is, use today's date.
   If dteBirthdate > Date Then
      dteBirthdate = Date
   End If

   ' Calculate the difference in years between today and birthdate.
   lngAge = DateDiff("yyyy", dteBirthdate, Date)
   ' If birthdate has not occurred this year, subtract 1 from age.
   If DateSerial(Year(Date), Month(dteBirthdate), Day(dteBirthdate)) > Date Then
      lngAge = lngAge - 1
   End If
   CalcAge = lngAge
End Function

< Previous | Top | Next >

Leading the way in Microsoft Office Development

VBA

Date Data Type

VBA provides the Date data type to store date and time values. The Date data type is an 8-byte floating-point value, so internally it's the same as the Double data type. The Date data type can store dates between January 1, 100, and January 1, 9999.

The decimal portion of a date represents the amount of time that has passed since midnight. For example, if the decimal portion of a date value is .75, three-quarters of the day has passed, and the time is now 6 P.M.

Because the integer portion of a date represents number of days, you can add and subtract days from one date to get another date.

Getting the Current Date and Time

Three functions in VBA can tell you exactly when it is: the Now, Date, and Time functions. The Now function returns both the date and time portions of a Date variable. For example, calling the Now function from the Immediate window returns a value like this one:

2/23/98 6:16:47 PM

Formatting a Date

You can use predefined formats to format a date by calling the FormatDateTime function, or you can create a custom format for a date by using the Format function.

The following procedure formats a date by using both built-in and custom formats:

Date Delimiters

When you work with date literals in your code, you need to indicate to VBA that a value is a date. If you don't, VBA may think you're performing subtraction or floating-point division.

For example, if you run the following fragment, the value that VBA assigns to the Date variable is not April 5, 1998, but 4 divided by 5 divided by 98. Because you're assigning it to a Date variable, VBA converts the number to a date, and prints "12:11:45 AM" to the Immediate window:

Assembling a Date

To reassemble a date, you can use the DateSerial function. This function takes three integer arguments: a year, a month, and a day value. It returns a Date value that contains the reassembled date.

Often you can break apart a date, perform a calculation on it, and reassemble it all in one step. For example, to find the first day of the month, given any date, you can write a function similar to the following one:

The following procedure uses the same strategy to return the last day of a month, given a date:

Both of these procedures are available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.

VBA also provides functions that you can use to disassemble and reassemble a time value in the same manner. The Hour, Minute, and Second functions return portions of a time value; the TimeSerial function takes an hour, minute, and second value and returns a complete time value.

Getting Part of a Date

The previous section showed how to return the year, month, and day from a date. You can get other information about a date as well, such as what quarter or week it falls in, or what day of the week it is.

The Weekday function takes a date and returns a constant indicating on what day of the week it falls. The following procedure takes a date and returns True if the date falls on a workday—that is, Monday through Friday—and False if it falls on a weekend.

This procedure is available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.

Adding and Subtracting Dates

This procedure is available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.

To find the interval between two dates, you can use the DateDiff function. The interval returned can be any of several units of time: days, weeks, months, years, hours, and so on.

The following example uses the DateDiff function to return the day number for a particular day of the year. The procedure determines the last day of the last year by using the DateSerial function, and then subtracts that date from the date that was passed in to the procedure.

Calling this procedure with the value of #2/23/98# returns "54."

Calculating Elapsed Time

To remedy this situation, the procedure checks to see whether the birthday has already occurred this year, and if it hasn't, it subtracts 1 to return the correct age.