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