Excel VBA: Number of Specified Days in a Specified Month

Got any Excel/VBA Questions? Free Excel Help

The function below can be used to determine how may days (e.g. Mondays etc.) there are in any specified month. For example:

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

will return 5 as there are five Wednesdays in the month of December in 2003

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

will return 4 as there are four Thursdays in the month of December in 2003

To be able to use this custom function in a Workbook, you must first place the code below into a standard module.

Open The Workbook.
Go To Tools>Macro>Visual Basic Editor (Alt+F11).
Then To Insert>Module.
Paste In The Code.
Click The Top Right X To Return To Excel.

'The Code

Function HowManyDaysInMonth(FullDateAs 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"quot;

				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

Now simply enter the function into any cell as shown above.