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.
