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