To find the last weekday date in a month:

=DATE(YEAR,MONTH+1,1)-WEEKDAY(DATE(YEAR,MONTH+1,1)+X)

Where X =

1 - Fri
2 - Thur
3 - Wed
4 - Tue
5 - Mon
6 - Sun
7 - Sat

So, for the last Monday in May 2007:

=DATE(2007,5+1,1)-WEEKDAY(DATE(2007,5+1,1)+5)

Biff

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