Ozgrid, Experts in Microsoft Excel Spreadsheets

Return Last Chosen Day of Given Month


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;


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



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.

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

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