Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 77 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
settings12/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.