Finding a Specific Date

In this section, you’ll find solutions to many simple problems that involve locating a date. Specifically, the routines include

Returning the first or last day of a specified month
Returning the first or last day of the week, given a date
Returning the first or last day of the year, given a date
Returning the first or last day of the quarter, given a date
Returning the next or previous specific weekday, given a date
Finding the next anniversary date
Returning the date of the nth particular weekday (Monday, Tuesday, and so on) of a month
Returning the next or previous working day, given a date
Returning the first or last working day of a specified month

Using Optional Parameters

Many of the procedures in the following sections accept one or more optional parameters. In each case, if you don’t specify the parameter in your function call, the receiving function assigns that parameter a value. In most cases, this allows you to omit the date parameter, and the function assumes the current date when it runs.

When you use optional parameters, you have two basic choices:

Use a Variant parameter, and check for the parameter using the IsMissing function.
Use a strongly typed parameter, and assign a default value in the formal declaration.

We’ve opted for the second alternative because this allows for type checking when calling the procedure. On the other hand, this technique also removes the possibility of using the IsMissing function to check for the omission of the parameter. Because the value you assign to the parameter in the formal declaration can only be a constant, not a function value, our solution when working with dates was to use the value 0 to indicate that you’d omitted the date parameter. For example, you’ll see declarations like this:

Function dhFirstDayInMonth( Optional dtmDate As Date = 0) _  As Date

This requires the procedure to check for the 0 value and replace it with the current date:

If dtmDate = 0 Then
    ' Did the caller pass in a date? If not, use
    ' the current date.
    dtmDate = Date
End If

We assumed you would be very unlikely to ever actually use the date 0 (12/30/1899) as a parameter to one of these procedures. If you do attempt to send 12/30/1899 to any of the procedures that accept an optional date parameter, the procedure will treat your input as though you’d entered the current date. If you must allow that date as input, you’ll need to either remove the optional parameter or find some other workaround.