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