Microsoft Excel

Determine the Number of Specified Days in Any Month

When you're creating calendar-related applications, especially payroll applications, you sometimes need to know how many times a given day of the week appears in a particular month.

Although Excel has many date and time functions, at the time of this writing, it does not have a date and time function that will, for example, tell you how many Mondays are in the month of January in the year 2005. You could use a very deeply nested variety of Excel's date and time functions to figure this out, but unfortunately, as you can imagine, this would be very cumbersome and awkward to reproduce.

This is a case in which VBA can simplify a complicated task. Instead of fumbling with complex functions, you can write a custom function that will do the same thing, and all you need to do is input the day and date for which you want a count.

You can use the following function to determine how may days are in any specified month. For example:

=HowManyDaysInMonth("1/12/03","wed")

will return 5, as there were five Wednesdays in the month of December in 2003. (Note that the date format should match your local settings-12/1/03 in the United States, for instance. The date format in the example is from Australia.)

Similarly, the following function:

=HowManyDaysInMonth("1/12/03","thu")

will return 4, as there were four Thursdays in the month of December in 2003.

To use this custom function in a workbook, you must first place the following code into a standard module, so open the workbook into which you want to place the code and select Tools » Macro » Visual Basic Editor (Alt/Option-F11). Then select Insert » Module and paste in the following code:

'The Code
Function HowManyDaysInMonth(FullDate As String, sDay As String) As Integer
Dim i As Integer
Dim iDay As Integer, iMatchDay As Integer
Dim iDaysInMonth As Integer
Dim FullDateNew As Date
iMatchDay = Weekday(FullDate)
    Select Case UCase(sDay)
       Case "SUN"
        iDay = 1
       Case "MON"
        iDay = 2
       Case "TUE"
        iDay = 3
       Case "WED"
        iDay = 4
       Case "THU"
        iDay = 5
       Case "FRI"
        iDay = 6
       Case "SAT"
        iDay = 7
    End Select
  iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
            (Year(FullDate), Month(FullDate) + 1, 1)))
  FullDateNew = DateSerial(Year(FullDate), Month(FullDate), iDaysInMonth)
        For i = iDaysInMonth - 1 To 0 Step -1
            If Weekday(FullDateNew - i) = iDay Then
                HowManyDaysInMonth = HowManyDaysInMonth + 1
            End If
        Next i
End Function

Close the window to return to Excel.

Now simply enter the function into any cell as shown earlier, and Excel will return a number that represents how many times the specified day occurred in the specified month.

by BrainBellupdated
Advertisement: