Finding the Beginning or End of a Quarter

Finding the beginning or end of a quarter takes a bit more effort than do the other functions in this section because there’s little support for working with quarters (January though March, April through June, July through September, October through December) in the VBA function library. Listing 2.4 shows the functions that solve this problem, dhFirstDayInQuarter and dhLastDayInQuarter.

Listing 2.4: Find the First and Last Day in a Quarter

Function dhFirstDayInQuarter( _
 Optional dtmDate As Date = 0) As Date
    ' Returns the first day in the quarter specified
    ' by the date in dtmDate.
    Const dhcMonthsInQuarter As Integer = 3
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhFirstDayInQuarter = DateSerial( _
     Year(dtmDate), _
     Int((Month(dtmDate) - 1) / dhcMonthsInQuarter) * _
     dhcMonthsInQuarter + 1, _
     1)
End Function
Function dhLastDayInQuarter(Optional dtmDate As Date = 0) As Date
    ' Returns the last day in the quarter specified
    ' by the date in dtmDate.
    Const dhcMonthsInQuarter As Integer = 3
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhLastDayInQuarter = DateSerial( _
     Year(dtmDate), _
     Int((Month(dtmDate) - 1) / dhcMonthsInQuarter) _
      * dhcMonthsInQuarter + (dhcMonthsInQuarter + 1), _
     0)
End Function

Certainly, once you know how to find the first day in the quarter, you know how to find the last; that’s just a matter of adding three months and subtracting one day. But how do you find the first day in the quarter containing a specified date? You know the year portion of the date (it’s the same as the date you’ve specified) and the day portion (which has to be 1), but what month do you use? You could, of course, use the brute-force technique, with a Select Case statement like this:

Select Case Month(dtmDate)
    Case 1, 2, 3
        intMonth = 1
    Case 4, 5, 6
        intMonth = 4
    ' etc.
End Select

But you just know there has to be a better way! This is one situation in which it’s worth pulling out some paper and thinking through what’s really going on. You may find it useful to create a table listing the input and output of a proposed calculation, in this case, to convert from any month to the first month in that quarter:

Month	First Month of Quarter
1	1
2	1
3	1
4	4
5	4
6	4
7	7
8	7
9	7
10	10
11	10
12	10

Remember, you’re looking for a mathematical relationship between the two columns. (Reminds you of high school algebra, right?) It looks as though each output “step” is a multiple of 3, plus 1. After much scribbling, you might come up with the following algebraic relation between the two columns, which turns out to be the exact solution dhFirstDayInQuarter uses:

First Month of Quarter = Int((Month - 1) / 3) * 3 + 1

This expression finds, for each month value, the largest multiple of 3 less than or equal to the number, multiplies the result by 3, and then adds 1. This calculation, based on the value in the first column, returns the value in the second column in every case. Therefore, rather than asking VBA to perform a lookup and a jump for each call to the function, it performs a moderately simple calculation.

Once dhFirstDayInQuarter has found the first month in the quarter, finding the first day is simple: the function calls DateSerial, building a date from the supplied year, the calculated month, and the day value 1. To find the last day in the quarter, dhLastDayInQuarter repeats the calculation from dhFirstDayInQuarter, adds 1 to the month it calculated to move to the next month, and then uses 0 for the day value. As discussed in the section “Finding the Beginning or End of a Month” earlier in this chapter, supplying 0 for the Day parameter to DateSerial returns the final day of the previous month, which is exactly what you want in this context.