Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Determine nth Weekday of a Specified Date

Excel VBA: Determine nth Weekday of a Specified Date in Excel

Lot's of free Excel VBA . Got any Excel Questions? Free Excel Help

Determine Which Weekday of a Specified Date. See Also: Days in Month

If you need to have formula return different results on the 1st Friday (any weekday) than if it would on the 2nd, 3rd or 4th Friday you can use the Custom Function/Formula below. For example, you may need to determine how many Saturdays have passed so far in a specified month. If the optional sDate argument is left empty the Custom Function uses the current date.

You could use the formula in any cell like shown below;

=nthDayOfMonth("tue") Will return the number of Tuesdays that have passed in the month so far, based on the current date.

=nthDayOfMonth("tue","12/12/2005") Will return the number of Tuesdays that have passed in the 12th month by the 12th day, based on the date 12/12/2005

Function nthDayOfMonth(sWeekDay As String, Optional sDate As String)

Dim lDay As Long

Dim lDayCount As Long

Dim lWeekDay As Long

Dim lDaydDate As Long



    Select Case UCase(sWeekDay)

       Case Is = "MON"

         lWeekDay = 2

       Case Is = "TUE"

         lWeekDay = 3

       Case Is = "WED"

         lWeekDay = 4

       Case Is = "THU"

         lWeekDay = 5

       Case Is = "FRI"

         lWeekDay = 6

       Case Is = "SAT"

         lWeekDay = 7

       Case Is = "SUN"

         lWeekDay = 1

    End Select

    

    If sDate = vbNullString Then sDate = Date

    

    On Error Resume Next

    For lDay = 1 To Day(sDate)

        If WeekDay(DateSerial(Year(sDate), Month(sDate), lDay)) = lWeekDay Then

            lDayCount = lDayCount + 1

        End If

    Next lDay

    On Error GoTo 0

    

    nthDayOfMonth = lDayCount



End Function

To use the Custom Function/Formula in an Excel Workbook, go to Tools>Macro>Visual Basic Editor (Alt+F11). Then go to Insert>Module and paste in the code 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.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft