Finding the Beginning or End of a Month

Started by sukishan, Sep 04, 2009, 04:42 PM

Previous topic - Next topic

sukishan

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
A good beginning makes a good ending

Quick Reply

Warning: this topic has not been posted in for at least 120 days.
Unless you're sure you want to reply, please consider starting a new topic.

Note: this post will not display until it has been approved by a moderator.

Name:
Email:
Verification:
Please leave this box empty:
Type the letters shown in the picture
Listen to the letters / Request another image

Type the letters shown in the picture:

Shortcuts: ALT+S post or ALT+P preview