Finding the Beginning or End of a Year

Finding the first or last day in a year is simple, compared to the other functions in this section. Once you understand the DateSerial function, it’s just a matter of building up a date value that’s January 1 or December 31 in the specified year. Because those dates are fixed as the first and last days in the year, no more calculation is necessary. The dhFirstDayInYear and dhLastDayInYear functions, in Listing 2.3, show all that’s necessary.

Listing 2.3: Find the First or Last Day in a Year

Function dhFirstDayInYear(Optional dtmDate As Date = 0) As Date
    ' Return the first day in the specified year.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhFirstDayInYear = DateSerial(Year(dtmDate), 1, 1)
End Function
Function dhLastDayInYear(Optional dtmDate As Date = 0) As Date
    ' Return the last day in the specified year.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhLastDayInYear = DateSerial(Year(dtmDate), 12, 31)
End Function

To call either of these functions, either pass no value (to work with the current year) or pass a date value indicating the year. The functions will each return the requested date. For example, the following code fragment calculates the first and last days in two ways:

Debug.Print "First day in the current year: " & _
 dhFirstDayInYear()
Debug.Print "Last day in the current year: " & dhLastDayInYear()
Debug.Print _
 "First day in the next year: " & _
 dhFirstDayInYear(DateAdd("yyyy", 1, Date))
Debug.Print _
 "Last day in the previous year: " & _
 dhLastDayInYear(DateAdd("yyyy", -1, Date))

© 1997 by SYBEX Inc. All rights reserved.