# Excel VBA: Number of Specified Days in a Specified Month

Back to: Excel VBA . Got any Excel/VBA Questions? Free Excel Help

Number of Specified Days in a Specified Month. See Also: Determine nth Weekday of a Specified Date || Return Last Chosen Day of Given Month

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.

1. Open the Workbook.
2. Go to Tools>Macro>Visual Basic Editor (Alt+F11).
3. Then to Insert>Module.
4. Paste in the code.

'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.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.