FREE Excel STUFF
SearchSearch Excel Content
Excel Help. Popular
NEW! Multiple Excel Search & Links
Excel Formulas
Excel Macros
Excel Newsletter
PRODUCTS
Up to $139.00 FREE!
Categories & SearchSearch for software
Excel Templates
Excel Add-ins
Excel Training
More....
OTHER
Excel Development


Excel VBA: Number of Specified Days in a Specified Month


NEW! More Books..
Add to Google advanced search! Free Help!

Add Excel Answers & Search To Your Google Toolbar Details

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!

Back to: Excel VBA . Got any Excel/VBA Questions? Free Excel Help

Number of Specified Days in a Specified Month. See Also: Determine nth Weekday of a Specified Date || Return Last Chosen Day of Given Month

The function below can be used to determine how may days (e.g Mondays etc) there are in any specified month. For example

=HowManyDaysInMonth("1/12/03","wed")

will return 5 as there are five Wednesdays in the month of December in 2003

=HowManyDaysInMonth("1/12/03","thu")

will return 4 as there are four Thursdays in the month of December in 2003

To be able to use this custom function in a Workbook, you must first place the code below into a standard module.

  1. Open the Workbook.
  2. Go to Tools>Macro>Visual Basic Editor (Alt+F11).
  3. Then to Insert>Module.
  4. Paste in the code.
  5. Click the top right X to return to Excel.

'The Code

Function HowManyDaysInMonth(FullDateAs String, sDay As String) As Integer
Dim i As Integer
Dim iDay As Integer, iMatchDay As Integer
Dim iDaysInMonth As Integer
Dim FullDateNew As Date

	iMatchDay = Weekday(FullDate)
		Select Case UCase(sDay)
 			Case "SUN"
				iDay = 1
			Case "MON"
				iDay = 2
			Case "TUE"quot;
				iDay = 3
			Case "WED"
				iDay = 4
			Case "THU"
				iDay = 5
			Case "FRI"
				iDay = 6
			Case "SAT"
				iDay = 7
			End Select
			
	iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
		(Year(FullDate), Month(FullDate) + 1, 1)))
		
	FullDateNew = DateSerial(Year(FullDate), Month(FullDate), iDaysInMonth)
	
	For i = iDaysInMonth - 1 To 0 Step -1
		If Weekday(FullDateNew - i) = iDay Then
			HowManyDaysInMonth = HowManyDaysInMonth + 1
		End If
	Next i
End Function

Now simply enter the function into any cell as shown 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!

Add to Google Search Tips FREE Excel Help

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