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:

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:

This function will return 29-Oct-2007.