VB makes it easy to calculate the last day of the month

Programmers often need to calculate the last day of the month for such purposes as accounting systems and employee time entry applications. I have noticed programmers using various tricks to do this, often using semi-hardcoded functions that calculate the last day of the month in the following way: first, the function checks the month of the given date. If the month is January, March, May, July, August, October, or December, the function returns 31. If the month is April, June, September, or November, the function returns 30. If the month is February, the function returns 28. The code looks something like this:
Private Function SetDates (pDate As Date) As String
 Select Case Month (pDate)
  Case 1, 3, 5, 7, 8, 10, 12
   SetDates = “31”
  Case 4, 6, 9, 11
   SetDates = “30”
  Case 2
   If (Year(pDate) Mod 4) = 0 Then
    SetDates = “29”
   Else
    SetDates = “28”
   End If
 End Select
End Function

In this article, I’ll show you a much simpler and more elegant solution to determining the last day of a month.

Let’s code
First, we’ll create a simple VB project to figure out the last and first day of any given month via the following steps:

1. 1.      Fire up VB and start a new project.
2. 2.      Add three labels—a Date And Time Picker Control (dtDate) and two text boxes (txtMonthStart and txtMonthEnd). Set the Enabled property of the text boxes to False. Your screen should resemble Figure A.

Figure A

Input screen

1. 3.      Add the following code to the dtDate_Change() event:

Call SetDates

1. 4.      Add the following code to the Form_Load() event:

dtDate.Value = Date ‘get today’s date
Call SetDates

1. 5.      Add Private Sub SetDates() with the following code:

Dim sDayFirst As String, sDayLast As String, sMonth As String, sYear As String
sDayLast = GetLastDayOfMonth(dtDate.Value)
sDayFirst = “01”
sYear = Year(dtDate.Value)
sMonth = Month(dtDate.Value)
txtMonthEnd.Text = sMonth & “/” & sDayLast & “/” & sYear
txtMonthStart.Text = sMonth & “/” & sDayFirst & “/” & sYear

1. 6.      Add Private Function GetLastDayOfMonth(pDate As Date) with the following code:

GetLastDayOfMonth = Day(DateSerial(Year(pDate), Month(pDate) + 1, 0))

1. 7.      Press [Ctrl][F5] to run the project, and you’ll see a screen like the one in Figure B.

Figure B

Output screen

Now, change the date at the top of the form to a different month and/or year, and you will see that the values in the two text boxes have been updated to show the first and last days of the month of the date you specified. Let’s take a closer look at how the code actually works.

Under the hood
Figuring out the last day of the month is a fairly simple task. On the Form_Load event, we set the value of the dtDate control to today’s date. After that, we call the SetDates procedure, where we call a function to get the last day of the month (GetLastDayOfMonth).

We pass the value of dtDate to GetLastDayOfMonth. After GetLastDayOfMonth returns the last day of a particular month, we set the text properties of txtMonthEnd and txtMonthStart to a full date by creating a string with the month value selected in the dtDate, the day value returned from GetLastDayOfMonth for the txtMonthEnd, “01” for txtMonthStart, and the year value selected in the dtDate. We use “01” because any month will start on the first.

On the change event of the dtDate control, we also call the SetDates procedure to update the text properties of the text boxes.

Now let’s take a look at what happens in the function GetLastDayOfMonth. To get the last day of the month, we use the built-in VB function DateSerial, which returns a date value for a specified year, month, and day. The function expects three arguments (a year, a month, and a day). Later, we use the built-in Day function to figure out the actual value of the last day of the month.

A value of the dtDate is passed to the function. In GetLastDayOfMonth, we figure out the year and the month of a date passed (pDate). Then, we pass the following arguments to the DateSerial function: the year, the month (we add 1 to the month value), and 0. If today is 8/19/02, the value returned by the statement DateSerial(Year(pDate), Month(pDate) + 1, 0) would be 8/31/02. We use the Day function to pick up the day from the resulting date, which in our case would return 31.
GetLastDayOfMonth = Day(DateSerial(Year(pDate), Month(pDate) + 1, 0))

Notice, however, that we used Month(pDate) +1, which gives us the month value of 9, not 8, and we also specified the day value of 0. That’s the trick. The DateSerial function will interpret this and return the last day of the month 8, not 9, which is exactly what we want to get.

In this sample, we demonstrated a simple way to get the last day of a month with only a few lines of code. Next time, we will look at other code examples to help simplify your code. The complete example code is available in Listing A.