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

Return Last Chosen Day of Given Month

| | Information Helpful? Why Not Donate.

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

Excel VBA: Return Last Chosen Day of Given Month. Display Last Specified Day of Given Month

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

Return Last Chosen Day of Given Month. See Also: Determine nth Weekday of a Specified Date . Lots More Custom Excel Functions

The Custom Function below can be used to return the last specified day of any given month.

It can be used in any cell like;

=LastDayOfMonth("Mon","10/10/2005")

Where "Mon" is the day we need to return the date of the last Monday in the Month October.

Syntax

=LastDayOfMonth(Which_Day,Which_Date)

Which_Day is a text abbreviation on any day, e.g. "Sat"

Which_Date is a text representation of any valid date, e.g. "10-Oct-2005", "10-10-2005" etc

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

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

'The Code

Function LastDayOfMonth(Which_Day As String, Which_Date As String) As DateDim i As IntegerDim iDay As IntegerDim iDaysInMonth As IntegerDim FullDateNew As DateWhich_Date = CDate(Which_Date)          Select Case UCase(Which_Day)            Case "SUN"                iDay = 1            Case "MON"                iDay = 2            Case "TUE"                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(Which_Date), Month(Which_Date) + 1, 1)))           FullDateNew = DateSerial(Year(Which_Date), Month(Which_Date), iDaysInMonth)       For i = 0 To iDaysInMonth        If Weekday(FullDateNew - i) = iDay Then            LastDayOfMonth = FullDateNew - i            Exit For        End If    Next iEnd Function

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