Floating Dates

There are 2 types of floating dates.

To get the second Sunday in May use the below formula, where cell A1 is the year (2004), cell A2 is 5 (May) and cell A3 is the day (1 for the first Sunday, 2 for the first Monday...the maximum number will be 35 which be equal to the fifth Saturday (assuming the month actually has 5 Saturdays)

=IF(MONTH(DATE(A1,A2,1))<>MONTH(IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))),"",IF(WEEKDAY(DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3))<WEEKDAY(DATE(A1,A2,1)),DATE(A1,A2,1)+(A3-WEEKDAY(DATE(A1,A2,1)))+7,DATE(A1,A2,1)-(WEEKDAY(DATE(A1,A2,1))-A3)))

Last Weekday

If you wanted the last weekday of a month, use this formula (This formula calculates as above except there are only 7 days to choose from, Sunday = 1, Monday = 2.... Saturday = 7)

=IF(A2<>MONTH(DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3)),DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3)-7,DATE(A1,A2+1,0)-(WEEKDAY(DATE(A1,A2+1,0))-A3))

Improved!

A shortened version of the above formula for the last weekday was kindly suggested by Vicente Soler.

=DATE(A1,A2+1,0)-WEEKDAY(DATE(A1,A2+1,0))+A3+IF(WEEKDAY(DATE(A1,A2+1,0))<A3,-7,0)

Thanks Vicente!