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 Date

Dim i As Integer

Dim iDay As Integer

Dim iDaysInMonth As Integer

Dim FullDateNew As Date



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



End Function

Now simply enter the function into any cell as shown above.

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

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 special@ozgrid.com 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 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

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS