Excel VBA: Number of Specified Days in a Specified Month

< Back to Search results

 Category: [Excel]  Demo Available 

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:


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


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.

  1. Open The Workbook.
  2. Go To Tools>Macro>Visual Basic Editor (Alt+F11).
  3. Then To Insert>Module.
  4. Paste In The Code.
  5. 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.


See also:

Excel AutoFilters in VBA using Dates
Add Excel Worksheets in Month/Monthly Order
Automatically Add Date, Time or Date & Time
Use AutoFilter in Excel VBA to Filter by Date & Time
Convert to a Currency Data Type


Free Training Course: Lesson 1 - Excel Fundamentals


See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)