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