Determine the Number of Specified Days in Any Month
When you're creating calendar-related applications, especially payrollapplications, 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 howmany Mondays are in the month of January in the year 2007. You could use a very deeply nested set 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 many days are in any specified month. For example:
=HowManyDaysInMonth("1/12/07","wed")
will return 4, as there were four Wednesdays in the month of December in 2007. (Note that the date format should match your local settings-12/1/07 in the United States, for instance. The date format in the example is from Australia.)
Similarly, the following function:
=HowManyDaysInMonth("1/12/07","thu")
will return 4, as there were four Thursdays in the month of December in 2007.
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 press Alt/Option-F11, or else right-click on the Sheet tab and select ViewCode (pre-2007, Tools → Macro → Visual Basic Editor). 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, then save your workbook. Now simply enter the function into any cell as shown earlier, and Excel will return a number that represents howmany times the specified day occurred in the specified month.