<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>
Ozgrid, Experts in Microsoft Excel Spreadsheets

Determine nth Weekday of a Specified Date

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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 LongDim lDayCount As LongDim lWeekDay As LongDim 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 = lDayCountEnd 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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates