This approach may appeal after seeing that formula you wrote.
Paste the following vba code in a standard module and then enter
"= LastDay(A1)" in any cell. The cell reference entered in the
formula should contain a valid date within the month you are searching.

So if A1 contains "=Today()" the answer shown in the cell is 11/ 27/ 2006.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Function LastDay(ByRef dteDay As Variant) As Variant
'Jim Cone - San Francisco, USA - November 2006
'Finds the last particular weekday in a month,
'given a valid date containing the month.
'A weekday in any month is limited to a maximum of 5
Dim D As Long
Dim N As Long
Dim lngLast As Long
Dim lngAnswer As Long
Const dteDesired As Long = vbMonday 'CHANGE as needed
Const lngOccurance As Long = 5 'Use 5 for last occurance - CHANGE as needed

If IsDate(dteDay) = False Then
LastDay = "Please enter valid date"
Exit Function
Else
dteDay = DateSerial(Year(dteDay), Month(dteDay), 1)
End If
lngLast = 32 - Day(dteDay - Day(dteDay) + 32)

For D = 0 To lngLast
lngAnswer = Weekday(dteDay + D)
If lngAnswer = dteDesired Then
If (Day(dteDay) + D) lngLast Then Exit For
LastDay = dteDay + D
N = N + 1
If N = lngOccurance Then Exit For
End If
Next
End Function
'-----------------


"OC"
wrote in message
I built a self adjusting work schedule and calendar and thought I'd throw in
the holidays from outlook's holiday file. Then I decided to work out the
formulas to have the dates adjust automatically. Worked out great until I
got to Memorial Day and Easter. I decided to scrap Easter due to it being
based on the lunar cycle, had no idea how to calculate that one. It turns
out all the rest had a set date or were a set day per week of month. When it
comes to Memorial Day however the rule is the last Monday of May. Well this
changes between the 4th and 5th weeks and i can't figure it out.

Here's an example of the formula I use for Mother's Day which is the second
Sunday in May:

=IF(DATE(YEAR(NOW()),5,1)+7-WEEKDAY(DATE(YEAR(NOW()),5,1)-DAY(DATE(YEAR(NOW()),5,1))+8-1)+(2-1)*7DATE(YEAR(NOW()),MONTH(NOW()),DAY(1)),DATE(YE AR(NOW()),5,1)+7-WEEKDAY(DATE(YEAR(NOW()),5,1)-DAY(DATE(YEAR(NOW()),5,1))+8-1)+(2-1)*7,DATE(YEAR(NOW())+1,5,1)+7-WEEKDAY(DATE(YEAR(NOW())+1,5,1)-DAY(DATE(YEAR(NOW())+1,5,1))+8-1)+(2-1)*7)


Any help you can give me is as always apprciated...