Using a Custom Function
The following custom function will return the last specified day of any given month (for example, the last Monday of the month).
To use it, right-click on your sheet name and select ViewCode (or Alt-F11).
Then, select Insert → Module and paste the following 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
Press the Close button to return to Excel proper, then save your workbook. Then, click in cell C6 and enter the following formula:
=LastDayOfMonth(Which_Day,Which_Date)
where Which_Day is a text abbreviation of any day (e.g., Sat) and Which_Date is a text representation of any valid date (e.g., 10-Oct-2007, 10-10-2007, etc). For example, use the following formula to calculate the last Monday in October 2007:
=LastDayOfMonth("Mon","10/10/2007")
This function will return 29-Oct-2007.