Microsoft Office 2000/Visual Basic Programmer's Guide

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.

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.

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."

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.