Finding the Beginning or End of a Month
Finding the first day in a specific month is easy: use the DateSerial function, breaking out the year and month portions of the specified date, asking for the day value 1. The dhFirstDayInMonth function, in Listing 2.1, performs this function call after first checking the incoming parameter and converting it to the current date if necessary. Calling the function as
dhFirstDayInMonth(#5/7/70#)
returns 5/1/70, of course.
Determining the last day in the month requires using an obscure, but documented, detail of the DateSerial function. It turns out that any (or all) of the three parameters to the DateSerial function can be numeric expressions. Because VBA will never return an invalid date, you can request the day before the first day of a month by incrementing the month value by 1 and decrementing the day by 1. The dhLastDayInMonth function in Listing 2.1 does just that. Using this expression:
DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
it finds the 0th day of the following month, which is, of course, the final day of the requested month.
Listing 2.1: Find the First or Last Day in a Month
Function dhFirstDayInMonth(Optional dtmDate As Date = 0) As Date
' Return the first day in the specified month.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
dhFirstDayInMonth = DateSerial(Year(dtmDate), _
Month(dtmDate), 1)
End Function
Function dhLastDayInMonth(Optional dtmDate As Date = 0) As Date
' Return the last day in the specified month.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
dhLastDayInMonth = DateSerial(Year(dtmDate), _
Month(dtmDate) + 1, 0)
End Function