# OzGrid

Excel VBA: Number of Specified Days in a Specified Month

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:

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

Free Training Course: Lesson 1 - Excel Fundamentals

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.

### Gallery

stars (0 Reviews)