Determine nth Weekday of a Specified Date

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

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

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

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

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 special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

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